What I'm looking for is some visual basic code to repeat the simple append query below, but with a few tweeks.
Here's the outline of what I'm looking for... I've got an Access Table as follows:
Table 1:
Date, Site, A, B, C,
14/07/06 330B4 14 0.356 25
Each column A-C and onwards!! are numeric measurements like temperature, salinity ect. What I would like to is cross reference the title of each column to another table:
Table 2:
ID Measurement
1 A
2 B
3 C
So that I end up with a new table:
Table 3:
Date, Site, Data MeasurementID
14/07/06 330B4 14 A
14/07/06 330B4 0.356 B
14/07/06 330B4 25 C
Note: Table 2 and 3 linked by MeasurmentID 1-many. Both the site and the Date change, and there are multiple 'Data' for each 'MeasurementID' i.e. lots of As, Bs, and Cs.
Also n Table 1 there are gaps where there is no data for some measurements.
At the moment I'm running an append query that operates on a single column taking data out of Table 1 and putting it into Table 3. This query satisfies the 1-many relationship by just manually adding in the number that corresponds to the measurement ID.
INSERT INTO Table 3 ( [Date], Site, Data, MeasurementID)
SELECT [Table 1].Date, [Table 1].Site, [Table 1].A, "1",
FROM [Table 1]
WHERE ((([Table 1].A) IS Not Null));
Is there a way of automatically repeating this simple query for every measurement column (A-C) in Table 1 by cross referencing the column title to the - Table 2, ID - and then putting all that into Table 3?
Note: The measurement columns (A-C) in Table 1 are never in the same order, and don't follow the order of Table 2.
This is a stylized version of the tables/relationships that I'm using, but hopefully its clear enough to get across what I'm looking for. I've written some visual basic code before, but this has got me. Any help would be great!
Thanks
Start Free Trial
