SQL Queries

There are many ways you can do this. If you are more comfortable with Excel, then do the merge in Excel, it is much easier. In Access 97, under Tools, Office Link, there is an option for Analyze with Excel. Export all tables to Excel, them filterand combine them. It is as simple as cut and paste. If you are an experience Access 97 developer, you can either merge the tables permeantly (I would advice you to make a copy of your tables if you use this method) or you can write a query to export the needed data to Excel.

Ok. First of all, you have to relate those tables together in the relationship. Then, use the query wizard to select only the needed colums from each table. Access will generate the SQL statement for you. If you don't understand this, please letme the level of familarity you are with relational database concept and Access 97.

There is a critical piece of information you have not provided: what differentiates each of the three rows from each other? I would expect (based on your sample description) that there should be another column which would identify, say, the "sub row". For example, your first would be subrow 1, the second subrow2, and the third subrow 3. You could then build three queries (if there were up to three subrows), each selecting one subrow. Make a destination table (for temporary purposes) that would have your columns 3,4,5 repeated three times. You might even repeat the "subrow" column three times if you wanted. Then append the subrow1 query results to the table with an append query. Using two update queries, link the destination table toeach of the subrow queries (for subrows 2 & 3) and update the contents of the appropriate column3,4,5 in the destination table to the value in the linked subrow query.

Here's an idea (sloppy though it may be) for using the data as you've described it:

Using a copy of your data table, build a query to select the first row of columns 3,4,5 for each AA BB combination. Append the results to the same kind of destination table described above.

Next, use the same type query to delete all of those "first" records.

Now run the same query, except this time you will be selecting what are actually the second rows of data (values 4,5,6). Make an update query as described above for the other method to place in the "subrow 2" set of the 3 data columns.

Delete these values from your temporary copy of the table.

Run the query again. This time it will be selecting the third set of data. Build a query linking this version of the query with the destination table, but this time (as in the above example) add to the third subrow set of the columns 3,4,5.

I hope you can follow this. You'll have to do some experimenting.

I think the main problem is that the final recordset will have n*3+2 columns, where n is the number if records where the first 2 colmns are the same, and you don't know n up front!

Here is the approach I would take:
I would write a VB/ADO app tograb the first record from Access, query for matching rows, walk the result set writing out XL rows and columns. Repeat.

Please let me know if you'd like more detail if this approach sounds feasible.

It would definitely be in a loop. A nested loop actually (iterate the columns inside the row interation).

There should be no problem running the application over and over. For 1000 rows, I'd estimate it would run in just a couple of minutes.

This question was closed by the author