Putting Linked tables into one table

[gsingleton]

Hello,

I have multiple tables that are linked to excel. I am creating a product selection tool to make it easy to find the products contained all these linked tables. They are necessary as they contain pricing data and information necessary to be kept in excel which is regularly updated.

Is there a way to create one table containing data from all these linked tables? I tried using an append query but realise that when the linked tables are updated the table containing all the data wont be?

Thanks
Gemma

[JoeM]

If you want to keep it dynamic, then as you pointed out, Action Queries like Append and Update won't work. Try using an Union Query instead, which combines all the records from multiple tables into a single query (provided their structures are similar).

See here for details: http://office.microsoft.com/en-us/ac...010206109.aspx

[gsingleton]

I have created my query which works and did the job - thanks.

As the tables are linked there are a lot of blank records below the data. I now want it to delete all the blank records in the union query that lie between the ones with data in. I tried the following which didn't work, it deletes all the records not just the blank ones. Can anyone help me out?

SELECT [Arano LED].*
FROM [Arano LED]
UNION ALL
SELECT [Celino LED].*
FROM [Celino LED]
DELETE *
FROM [Query combine all]
WHERE ((([Query combine all].Range) Is Null));

with the aim to delete the blank rows from the query when it has put all the records together.
Or

SELECT [Arano LED].*
FROM [Arano LED]
UNION ALL
SELECT [Celino LED].*
FROM [Celino LED]
DELETE *
FROM [Arano LED]
WHERE ((([Arano LED].Range) Is Null))
DELETE *
FROM [Celino LED]
WHERE ((([Celino LED].Range) Is Null));

Which I think would be tedious as I add more tables.

[JoeM]

I don't think you will be able to run Delete Queries on Union Queries as Union Queries, by definition are not updateable (they are read-only).

When you say blank records, are all the fields blank, or just certain ones?
Why are their blank records? How did they get there?

I would either do you data clean-up on each table before the Union Query or simply include criteria in your Union query to filter that data out (not physically delete it, just don't return it).