Update Table

[Guest]

I have a table which needs to be updated with data from another table. The
problem is the second table has 2 data fields and I cannot link the 2
tables...........

Table one contains Transaction data such as [Date] [From] [To] [Trucker]
[Trucker Cost]. I need to update a field [FSC]. "Fuel Surcharge"

The rates are maintained in another table "FSC Rates" because the rate
changes weekly. It only has 2 data fields [Effective date], [FSC].

I need to update table one based on the [Date] of the activity and the
[Effective date] in the rate table.

ie: [Max of Date] where [Date] is less than the [Effective Date]

How do I perform this task?

Do I need to change my table structure?

Thanks

 

[Guest]

Try this query to find the effective FSC rate for each Date. Then us an
update query to do the updating.

P.S. You should use a different name for your field other than Date.

 

[Guest]

The Field name is not Date - I just used it to simplify my explanation.

Thanks
--
A MACKENZIE, CMA, MBA

Try this query to find the effective FSC rate for each Date. Then us an
update query to do the updating.

P.S. You should use a different name for your field other than Date.

I have a table which needs to be updated with data from another table. The
problem is the second table has 2 data fields and I cannot link the 2
tables...........

Table one contains Transaction data such as [Date] [From] [To] [Trucker]
[Trucker Cost]. I need to update a field [FSC]. "Fuel Surcharge"

The rates are maintained in another table "FSC Rates" because the rate
changes weekly. It only has 2 data fields [Effective date], [FSC].

I need to update table one based on the [Date] of the activity and the
[Effective date] in the rate table.

ie: [Max of Date] where [Date] is less than the [Effective Date]

How do I perform this task?

Do I need to change my table structure?

Thanks

 

[Guest]

Karl:

Not sure what "query" you refer to.

If the in: microsoft.public.access.queries is a link it doesn't take me
anywhere.

What am I doing wrong?


--
A MACKENZIE, CMA, MBA

Try this query to find the effective FSC rate for each Date. Then us an
update query to do the updating.

P.S. You should use a different name for your field other than Date.

I have a table which needs to be updated with data from another table. The
problem is the second table has 2 data fields and I cannot link the 2
tables...........

Table one contains Transaction data such as [Date] [From] [To] [Trucker]
[Trucker Cost]. I need to update a field [FSC]. "Fuel Surcharge"

The rates are maintained in another table "FSC Rates" because the rate
changes weekly. It only has 2 data fields [Effective date], [FSC].

I need to update table one based on the [Date] of the activity and the
[Effective date] in the rate table.

ie: [Max of Date] where [Date] is less than the [Effective Date]

How do I perform this task?

Do I need to change my table structure?

Thanks

 

[Guest]

OOPS!
Forgot to paste.

SELECT aMack.Date, Max([FSC Rates].[Effective date]) AS [FSC Effective
Date], Last([FSC Rates].FSC) AS [FSC Rate]
FROM aMack, [FSC Rates]
WHERE ((([FSC Rates].[Effective date])<=[date]))
GROUP BY aMack.Date;

Karl:

Not sure what "query" you refer to.

If the in: microsoft.public.access.queries is a link it doesn't take me
anywhere.

What am I doing wrong?


--
A MACKENZIE, CMA, MBA

Try this query to find the effective FSC rate for each Date. Then us an
update query to do the updating.

P.S. You should use a different name for your field other than Date.

I have a table which needs to be updated with data from another table. The
problem is the second table has 2 data fields and I cannot link the 2
tables...........

Table one contains Transaction data such as [Date] [From] [To] [Trucker]
[Trucker Cost]. I need to update a field [FSC]. "Fuel Surcharge"

The rates are maintained in another table "FSC Rates" because the rate
changes weekly. It only has 2 data fields [Effective date], [FSC].

I need to update table one based on the [Date] of the activity and the
[Effective date] in the rate table.

ie: [Max of Date] where [Date] is less than the [Effective Date]

How do I perform this task?

Do I need to change my table structure?

Thanks