One Query result from two Tables with one similar field

[shuddle]

I have two tables: LookupTbl_Rates and Tbl_Vehicle_Data

LookupTbl_Rates

LK_Rate_ID

LK_Tag

LK_Monthly_Rate

LK_Mileage_Rate

LK_OE_Monthly

LK_OE_Mileage

Tbl_Vehicle_Data

VehicleID

TAG

(plus other fields)

What I am trying to do is get the results in a query

VehicleID LK_Tag, LK_Monthly_Rate, LK_Mileage_Rate, LK_OE_Monthly, LK_OE_Mileage


I have the relationship between TAG and LK_Tag set.

The query I created will give the VehicleID or the LK values but not both.

I think I'm making it to complicated. Any help is appreciated.

[CJ_London]

What is the sql to your query?

[madpiet]

the one part you left out is the only part we need:
on what columns are you joining the two tables?

[Micron]

on what columns are you joining the two tables?

Would that not be

I have the relationship between TAG and LK_Tag set.

[mike60smart]

Hi
I think you need to add the following field "LK_Rate_ID" as a FK to your tbl_Vehicle_data

[shuddle]

Here is my Sql
SELECT Tbl_Vehical_Data.VehicleID, Tbl_Vehical_Data.TAG, LookupTbl_Rates.LK_Rate_ID, LookupTbl_Rates.LK_Tag, LookupTbl_Rates.LK_Monthly_Rate, LookupTbl_Rates.LK_Mileage_Rate, LookupTbl_Rates.LK_OE_Monthly, LookupTbl_Rates.LK_OE_Mileage
FROM Tbl_Vehical_Data LEFT JOIN LookupTbl_Rates ON Tbl_Vehical_Data.[TAG] = LookupTbl_Rates.[LK_Tag];


Ultimately I need the query to connect the vehicle ID to the rates. The rates changes. The vehicle ID and TAGs will not.

I will be overwriting new data into the LookupTbl_Rates each month which will change the LK_Rate_ID so I can't use that as a connector. I thought I could connect it on the tag number since it is constant.

This may not be doable the way I am wanting it to work.

[mike60smart]

Hi
Your "LookupTbl_Rates" should have 2 additional fields "DateFrom" & "DateTo"

Then when you use a Combobox to select a Rate you would filter to only show Rates which do not have a DateTo.

[Minty]

I will be overwriting new data into the LookupTbl_Rates each month which will change the LK_Rate_ID so I can't use that as a connector. I thought I could connect it on the tag number since it is constant.

This may not be doable the way I am wanting it to work.

I wouldn't overwrite this data. You can store the rate changes with valid from and to dates each time they change.
This means you can always look up the correct rate based on the transaction date.

It's much more robust method.

[CJ_London]

In your Tbl_Vehical_Data table design is TAG a lookup field?

[shuddle]

I found another solution to get the results I needed. I used a ME statement to autofill the rates into the main table. It works. Thanks for your help.

[Minty]

I found another solution to get the results I needed. I used a ME statement to autofill the rates into the main table. It works. Thanks for your help.

Can you show us, Me. isn't a function it's a way of referring to the current form object.