Add auto increment field to union query

[Kristina]

Hello,

I have a series of 9 identical tables which are linked as text files to my Access database. The content of these tables is manually pasted each day to the table from an external application which delivers the data in an inconvenient way: duplicate values are not repeated, eg if in the first column is the supplier name it appears once then for all the rest of the lines where the products are from that supplier the supplier name is blank. The second column is the product id and the same thing occurs.

I have written a union query to join all the tables together but the order of the lines is jumbled up which of course renders the data meaningless. I am unable to recreate the original order of the data through sorting so I am trying to add an autoincrement field to the union query. The plan would be that I would then be able to keep each of the tables in exactly its original order.

Can anybody explain to me how to do this?

By the way I also tried to add an auto-increment field to the link specification but I couldn't work out how to do that either - if that is an easier solution then I would be happy to learn how to do it.

 

[Tigs81]

I assume that you dont necessarily need an ID field you just need to separate the tables. By having text in as a field will allow it to group the information and not give you meaningless data.

SELECT "Table1" AS TableName,tblTable1.Field1, tbltable1,Field2
FROM tblTable1
UNION
SELECT "Table2" AS TableName,tblTable2.Field1, tblTable2.Field2
FROM tblTable2;

 

[Kristina]

It doesn't matter what the field is as long as the rows in the joined table are in exactly the same order as they are in the original table. The sql looks like this at the moment:

SELECT *, 1 as X
FROM [Tbl_SCP1 Procurement Plan]
UNION SELECT *, 2 as X
FROM [Tbl_SCP1 Procurement Plan SCP2]
UNION SELECT *, 3 as X
FROM [Tbl_SCP1 Procurement Plan SCP4]
etc

So you can see I have ordered the rows according to table - but they are still jumbled up within each table.

 

[apr pillai]

It looks like the data you have received is a converted version of Report Spool File that suppresses the duplicate values in subsequent lines. When they are taken in as data lines these will give problems everywhere.

The solution is to fill the blank fields with previous line values wherever they are necessary, before using the data for any reporting purposes. Manually this task is difficult, but we can do this with the help of a small VBA Routine.

Before going for this method make a back up of all the 9 files for safety, just in case something goes wrong you can recover the data from backup.

Run the following Code for each Table to scan the table and fill the empty area with the Supplier Name appearing above those blank lines:

Public Function FillSupplier(ByVal tblName As String)
Dim db As Database, rst As Recordset
Dim SupplierName As String

Set db = CurrentDb
Set rst = db.OpenRecordset(tblName, dbOpenDynaset)
Do While Not rst.EOF
    Do While IsNull(rst![Supplier]) And Not rst.EOF
         rst.Edit
         rst![Supplier] = SupplierName
         rst.Update
         rst.MoveNext
    Loop
    If Not rst.EOF Then
       SupplierName = rst![Supplier]
       rst.MoveNext
    End If
Loop
rst.Close
            
End Function

Modify the line rst![Supplier] with correct field name, if different.

You may call the Program for each Table from the Debug Window like:

FillSupplier "myTable1"

Once you modify all the tables this way you can work on the Union Query.

For more details on Union Query take a look at this Blog Post:http://www.msaccesstips.com/2008/02/union-query/