Updating Tables with differing fields....or something

[pdldavis]

Hi, I am unfortunatly on loan to the programming department and have a bit of a problem.

Table 1 has five fields and will always contain those five fields:

Date Date (first monday of the week) (Primary Key)
Electrical Numeric
Mechanical Numeric
Civil Numeric
Structural Numeric

This table is initially populated with Dates falling on Monday.

Table 2 is generated by using a cross-tab query, which appends its information to Table 2

The Date field in Table 2 is also a Primary key.

This table may or may not have the same number of fields, but it will always contain the same field headings.

Example:

It may only contain three fields. It will always have the date field.

Date Date
Electrical Numeric
Mechanical Numeric

The original intent was to run a simple update query, linking on the Date field to update the information in Table 1 from Table 2. This is then exported to Excel.

This only works when each table has the same number of fields, or you can end up clicking through fields that don't exist.

I am not really sure how to make this work.

....and I have a lot of latitude.

Any suggestions would be welcome.

Thanks, Dan

 

[PHV]

Table 2 is generated by using a cross-tab query
Why not posting the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dRahme]

Hi,

Why not make a copy of table 1 and append the cross tab results to it. You would then have identical tables. The rest should be pretty simple.

Hope that helps,

dRahme

 

[pdldavis]

Yes, that finally dawned on me too a couple days later.
It works now.

The way they coded it they were making a table directly from the cross-tab and were trying to update the first table based on that, which wasn't working.

Thanks for the reply though.

Dan