not sure on query relationships?

[Guest]

I did what was recommended below but am having an issue.

How do I incorporate the delivydname(tried adding it as a primary key and
get no records on the query)
I believe this should be Client specific. I am having an issues with having
the same yard numbers for different del yard NAMES

Example:

Prairie Material’s yard # 15(Delivydnum) is Addison(delivydName)

Ozinga Concrete’s yard #15(Delivydnum) is Hampshire(Delivydname).

See prior posting

I have a table that has a delivery yard, pickup yard, and type of
material(2choices). Based on that combination of what is in those three
fields there is a given RATE. On the form based on this table I would like
the rate to automatically fill based on the combination of the 3 fields
chosen. Seems like it should be easy not sure where I go with this.

Thanks,
Barb

I'd suggest creating a Rates table with four fields - these three as a
joint Primary Key and the corresponding rate. You can then create a
Query joining your current table to this rate table by the three
fields (two yards and a type) to look up the rate.

John W. Vinson[MVP]


thanks,
Barb

 

[Jeff Boyce]

Can you post the SQL of your query?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Here it is.

SELECT TankTicketEntry.TodaysDate, TankTicketEntry.ClientId, [Client
Table].ClientName, TankTicketEntry.Ticket, TankTicketEntry.ServiceDate,
TankTicketEntry.TankMat, TankTicketEntry.PickupYd,
tblTanksrate2.PickupydName, TankTicketEntry.DelivYd,
tblTanksrate2.DelivydName, TankTicketEntry.Divisor, TankTicketEntry.LB,
TankTicketEntry.GrossWeight, TankTicketEntry.Truck1,
TankTicketEntry.Employeecode1, tblTanksrate2.rate,
TankTicketEntry.InvoiceDate, TankTicketEntry.recordnumber,
TankTicketEntry.comments, [rate]*[grossweight] AS cartagepaid, [cartagepaid]
AS balancedue, tblTanksrate2.rate AS billrate
FROM (TankTicketEntry INNER JOIN [Client Table] ON TankTicketEntry.ClientId
= [Client Table].ClientId) INNER JOIN tblTanksrate2 ON
(TankTicketEntry.ClientId = tblTanksrate2.ClientId) AND
(TankTicketEntry.PickupYd = tblTanksrate2.Pickupyd) AND
(TankTicketEntry.DelivYd = tblTanksrate2.Delivyd) AND
(TankTicketEntry.TankMat = tblTanksrate2.Type);


Thanks,
Barb

 

[Jeff Boyce]

I don't know your data, so I can't speak to what may or may not be there...

I do know that your query includes joins, so if data is missing in one table
you could get less than what you're looking for.

If this were mine, I'd start out in query design mode and do only one table
(probably the one with the most selection criteria) to start. When that
piece was working correctly, I'd add in the next table...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Here it is.

SELECT TankTicketEntry.TodaysDate, TankTicketEntry.ClientId, [Client
Table].ClientName, TankTicketEntry.Ticket, TankTicketEntry.ServiceDate,
TankTicketEntry.TankMat, TankTicketEntry.PickupYd,
tblTanksrate2.PickupydName, TankTicketEntry.DelivYd,
tblTanksrate2.DelivydName, TankTicketEntry.Divisor, TankTicketEntry.LB,
TankTicketEntry.GrossWeight, TankTicketEntry.Truck1,
TankTicketEntry.Employeecode1, tblTanksrate2.rate,
TankTicketEntry.InvoiceDate, TankTicketEntry.recordnumber,
TankTicketEntry.comments, [rate]*[grossweight] AS cartagepaid, [cartagepaid]
AS balancedue, tblTanksrate2.rate AS billrate
FROM (TankTicketEntry INNER JOIN [Client Table] ON TankTicketEntry.ClientId
= [Client Table].ClientId) INNER JOIN tblTanksrate2 ON
(TankTicketEntry.ClientId = tblTanksrate2.ClientId) AND
(TankTicketEntry.PickupYd = tblTanksrate2.Pickupyd) AND
(TankTicketEntry.DelivYd = tblTanksrate2.Delivyd) AND
(TankTicketEntry.TankMat = tblTanksrate2.Type);


Thanks,
Barb

Can you post the SQL of your query?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/