Join 2 tables

[pbrown99]

Two tables need to be linked for query but having problems
Table 1 has vendor invoice info like:
Name, amount, date, and status (if the vendor is overseas the status is 1, otherwise 0)

Table 2 has the cargo charge percentages.
Since the percentages change, it has the fields:
Status, Start Date, End Date, Percentage

The problem is that I need to create a query that will look look at table 1 and based on if they are a status 1 or 0 and the date of the invoice inculde the correct precent.

I have not been able to link the tables correctly and am unsure of how to do criteria if I was to leave the tables unlinked. Any thoughts?


Thank you for any and all help!

 

[traingamer]

Does Table 2 have a primary key?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[pbrown99]

No.... I could make an autonumber but that is about it. Since status 1 & 0 occassionally share either the same start or end date. Percent can not be one since the same percent may be used aswell.

Thank you for any and all help!

 

[grande]

I tried this on some stupid test data, and it seems to work well.

SELECT vendor.Name, percentages.Percentage, vendor.Date
FROM vendor INNER JOIN percentages ON vendor.Status = percentages.Status
WHERE (((percentages.Date) Between [Start Date] And [End Date]));

Hope this helps!

-------------------------
Just call me Captain Awesome.

 

[traingamer]

Try this (tested):

SELECT tblVendorInvoice.Name, tblVendorInvoice.Amount, tblVendorInvoice.Status, tblVendorInvoice.Date, tblCargo.Percentage
FROM tblVendorInvoice LEFT JOIN tblCargo ON tblVendorInvoice.Status = tblCargo.Status
WHERE (((tblVendorInvoice.Date)>=[tblCargo].[startDate] And (tblVendorInvoice.Date)<=[tblCargo].[EndDate]));

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[pbrown99]

Combinations are great, thanks! I used a inner join from the first code but the second where statement and it all works great! Thank you both!