not sure on query relationships?

B

babs

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
 
B

babs

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
 
J

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/

babs said:
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 said:
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/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top