General discussion

Locked

SQL Queries

By bowjangles ·
I need to find out how to take a table in this format:

Column1 Column2 Column3 Column4 Column5

AA BB 1 2 3
AA BB 4 5 6
AA BB 7 8 9

And put the information into this format:

AA BB 1 2 3 4 5 6 7 8 9

I need to combine all records that have the same first two fields. In this example is turning three records into one. But this should be outputted to a Microsoft Excel spreedsheet and i am working in Access 97.
Any ideas would be very helpful because i have completely drawn a blank.

Thanks
Justin

This conversation is currently closed to new comments.

12 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL Queries

by elingy In reply to 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.

Collapse -

SQL Queries

by bowjangles In reply to SQL Queries

The problem is writing the query! I have no idea how to write a query that will take those three records and combine them to make one longer record. If you know how to do this, it would be greatly appreciated!

Justin

Collapse -

SQL Queries

by elingy In reply to SQL Queries

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.

Collapse -

SQL Queries

by Shanghai Sam In reply to SQL Queries

I know Access '97 very well, but the Wizard doesn't help at all. I need help with SQL.

Collapse -

SQL Queries

by Bob Sellman In reply to SQL Queries

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.

Collapse -

SQL Queries

by Bob Sellman In reply to SQL Queries

I forgot to mention that to select the first record you must build a grouping query. In the query builder right click the sort row and select "totals". That row now becomes "Totals" and the sort column moves down one row.

In the columns 3,4,5 you can select "first" from the drop down list in the query builder's "totals" column to get the first values.

Good luck.

Collapse -

SQL Queries

by Shanghai Sam In reply to SQL Queries

I am having trouble understanding what you are telling me, but it is the right idea. The only thing is that there are way more records then three. Actually hundreds. For example:

AA BB 1 2 3
AA BB 4 5 6
AA1 BB1 11 22 33
AA1 BB1 44 55 66
AA2 BB2 111 222 333
AA3 BB3 12 23 45
AA3 BB3 67 89 01
AA3 BB3 23 45 67

And the pattern of the same number of records relating to the first few rows is always different. I could have up to five records based on the same fields and then have as little as one. The method you described seems as though it would be more for a small amount of records. Sorry for not mentioning this earlier.

Justin

Collapse -

SQL Queries

by andrew In reply to SQL Queries

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.

Collapse -

SQL Queries

by bowjangles In reply to SQL Queries

It sounds feasible if i am able to write that code in a loop. Because n can vary from 1 to 5. But the total number of records is around 953. And this number will probably slowly increase as time goes by. So, if i cannot put the code in a loop and i half to repeat what you just explained for each group, i will not be able to do this because it will take to long. But if you can expand on what you just said, i would appreciate it!

Thanks
Justin

Collapse -

SQL Queries

by andrew In reply to SQL Queries

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.

Back to Web Development Forum
12 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Software Forums