Tables and Download

[Guest]

Hello,

I have three tables.

Table One = Main Source of Data (All Account Numbers)
Table Two = Subdatasheet for Main Source Data (This tracks the Account
Numbers Monthly Balances By Month)
Table Three = A specific months Download from an outside system (A linked
Excel Sheet)

I want to get the data from table 3 to tables one and two.
QUERY 1: What I did was I ran a query to compare all the Account Numbers
from Table 3 to table 1. With those matched Account Numbers from table 3 I
have the account numbers monthly balance.

QUERY 2: So I tried to create a query from the QUERY 1 to add the data of
the monthly data.

For some odd reason it will not let me add data to the table 2. Remind you,
that table two is a subdatasheet.

Is there anyone that knows how to let me enter data automatically into table
2, becasue right now it is not letting me. Thanks

 

[Ron2005]

I am assuming that table three has month and account number in it.

1) Create sum query that groups by year/month and account and sums
balance
2) Create a table create query that uses query 1 to create a work table
3) Create an update query that matches table 2 with the work table and
updates the balance.

You may want to do some other checking and decide what to do about
accounts in table 3 that are not in table 1
And accounts and/or months that are not in table 2.

Ron

 

[Guest]

Hey Ron,

I accomplished steps one and two.

Now in step three.

Since the tables are linked by an Internal_Acct RecTracker and by Date
(which is month) I set those as links.

SQL format: UPDATE Maintenance LEFT JOIN [Table Download Query] ON
(Maintenance.Internal_AcctRecTracker = [Table Download
Query].Internal_AcctRecTracker) AND (Maintenance.Date = [Table Download
Query].Month) SET [Table Download Query].[G/L AMOUNT] =
[Maintenance].[Balance];


So when I try to do an update it updates the Table Download Query (from your
step two) and not the Maintenance Query, which is where I want the data to
go.

Thanks for your help.

 

[Ron2005]

In your original discription you say that Table Two = Subdatasheet for
Main Source Data.

There should not be another query involved.

I can't figure out what fields are what between your original
description and your statement of the query. Which table is
Maintenance. If it is table two then you have the update backwards.