Consolidating Multiple Tables

[lizardman2014]

Hi,

I really need some help, what I have is a database setup with multiple tables in which different areas of my DC can input information simutanously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.

I have tryed Union coding but always get Syntax Error etc.

Thanks

P.S I am happy to provide the database if that helps you to understand.

 

[namliam]

Select * from table1
UNION ALL
Select * from Table2
UNION ALL
Select * from Table3
UNION ALL
Select * from Table4

That should work just fine....

Regardless of the discussion if you shouldhave multiple copies of the same table as well as your use of VLookups in excel

And please refrain from using acronyms that are likely unknown to the (foreign) public.... I have NO clue what DC should stand for for example

 

[lizardman2014]

Apologies for the DC (distrubrution centre)..

I have tryed to run this code and recevied an Error.

I had:

SELECT Time, Location, Entry Name, Tote colour, other details
FROM Replen
UNION ALL
SELECT Time, Location, Entry Name, tote colour, other details
FROM Promo
UNION ALL
etc

But syntax error kept occuring is there something I am overlooking?
Thanks

 

[Simon_MT]

Reserved word Time?

Select Replen.Time, Replen.[Entry Name], Replen.[Tote colour], Replen[Other Details]
FROM Replen
UNION ALL etc

Brackets are required with feild names with spaces.

Simon

 

[lizardman2014]

Hi Simon,

Thanks but I am still getting 'Syntax Error in query.Incomplete query clause'

Below is the exact union I have. I must still be doing something wrong?

Select AFRAME.Time, AFRAME.[Entry Name], AFRAME.[Tote Colour], AFRAME.[Other Details], AFRAME.Location
FROM AFRAME
UNION ALL
Select GREENMILE.Time, GREENMILE.[Entry Name], GREENMILE.[Tote Colour], GREENMILE.[Other Details], GREENMILE.Location
FROM GREENMILE
UNION ALL
Select OTHER.Time, OTHER.[Entry Name], OTHER.[Tote Colour], OTHER.[Other Details], OTHER.Location
FROM OTHER
UNION ALL
Select PROMO.Time, PROMO.[Entry Name], PROMO.[Tote Colour], PROMO.[Other Details], PROMO.Location
FROM PROMO
UNION ALL
Select QA.Time, QA.[Entry Name], QA.[Tote Colour], QA.[Other Details], QA.Location
FROM QA
UNION ALL
Select REPLEN.Time, REPLEN.[Entry Name], REPLEN.[Tote Colour], REPLEN.[Other Details], REPLEN.Location
FROM REPLEN
UNION ALL

 

[namliam]

You end with a select statement, only Union all between the Selects...

Select AFRAME.Time, AFRAME.[Entry Name], AFRAME.[Tote Colour], AFRAME.[Other Details], AFRAME.Location
FROM AFRAME
UNION ALL
Select GREENMILE.Time, GREENMILE.[Entry Name], GREENMILE.[Tote Colour], GREENMILE.[Other Details], GREENMILE.Location
FROM GREENMILE
UNION ALL
Select OTHER.Time, OTHER.[Entry Name], OTHER.[Tote Colour], OTHER.[Other Details], OTHER.Location
FROM OTHER
UNION ALL
Select PROMO.Time, PROMO.[Entry Name], PROMO.[Tote Colour], PROMO.[Other Details], PROMO.Location
FROM PROMO
UNION ALL
Select QA.Time, QA.[Entry Name], QA.[Tote Colour], QA.[Other Details], QA.Location
FROM QA
UNION ALL
Select REPLEN.Time, REPLEN.[Entry Name], REPLEN.[Tote Colour], REPLEN.[Other Details], REPLEN.Location
FROM REPLEN

 

[lizardman2014]

That Worked Great thanks!

But I now face another challenge and that is now I have all the Info together how to I get it from that query into a linked Mircosoft Excel spreadsheet.

So basically when a user updates their table it will update the query and the excel table to be live viewing of the new data being entered?

thanks

 

[namliam]

cant do that live, always has to be a static snap shot...

Also, probably after the fact, having to use union queries like this is a strong suggestion of some design issues in your database

 

[Simon_MT]

We kmow that. But try creating another query using your co-joined information. There maybe a question of performance. Another alternative maybe to consider consolidating the tables with each source being identified.

Simon