Easy SQL Question for you all

[guitarist13]

Hello all,

I have been lurking around these forums for about a year now, and have started to use Access nearly every day for work, so think it is about time I became active.

If you don't mind, I have a quick question to start off my time here:

I have lots of Tables (50+) in Access which are all in the same format and have the same headings, so I need to find a way to consolidate them all without setting up append queries, and such.

Can I do one Select Query with an SQL statement that says something like:

---select all data from every table in this database---

And if so, what is the syntax I would need?

Part of the reason for something like this is that in the future these formats may all change, but luckily these tables will always be in the same format; and so an SQL statement like this will mean I will not have to edit the query at a later date.

Thanks!

 

[georgedwilkinson]

Look at the "Union" keyword.

I question your design, too. But there's so much you know about it that we don't.

 

[pbaldy]

There is no automatic SQL based solution that I can think of. You would want a UNION query:

SELECT Field1, Field2
FROM Table1
UNION ALL
SELECT Field1, Field2
FROM Table2
...

You could use VBA code stepping through the TableDef collection to create such a query. I'd question what the tables contained, as it sounds like the db is not normalized.

 

[guitarist13]

Thanks for replying. A quick summary of the whole thing:

I have lots of data of different listed company accounts going back about 5 years.

It starts in Excel where I have a sheet for each financial category, and the list of companies by row, and the year by column. These sheets are all the same.

I then have some VBA that creates a separate Access Table for each of these sheets, and now I want to consolidate all of this data into one table so that I can take it back to Excel in Pivot format so I can analysis the data as I want; ie. selecting different financial categories, building up various parts of the accounts, only displaying certain years, etc.

I can't think of a better way to create and then store the data, and make it very clear to everyone else I work with how numbers are built up and to make it easily expandable for anyone else to use.

Hence my need now to be able to consolidate these tables.

The tricky thing is, I will add more financial categories and years as I go on, which is fine for the VBA I use to take the Excel sheets into Access Tables, but not for manually writing SQL in Access to get everything into one Table.

Any suggestions would be great. Perhaps I have missed something obvious.

Thanks again!

 

[Vassago]

Thanks for replying. A quick summary of the whole thing:

I have lots of data of different listed company accounts going back about 5 years.

It starts in Excel where I have a sheet for each financial category, and the list of companies by row, and the year by column. These sheets are all the same.

I then have some VBA that creates a separate Access Table for each of these sheets, and now I want to consolidate all of this data into one table so that I can take it back to Excel in Pivot format so I can analysis the data as I want; ie. selecting different financial categories, building up various parts of the accounts, only displaying certain years, etc.

I can't think of a better way to create and then store the data, and make it very clear to everyone else I work with how numbers are built up and to make it easily expandable for anyone else to use.

Hence my need now to be able to consolidate these tables.

The tricky thing is, I will add more financial categories and years as I go on, which is fine for the VBA I use to take the Excel sheets into Access Tables, but not for manually writing SQL in Access to get everything into one Table.

Any suggestions would be great. Perhaps I have missed something obvious.

Thanks again!

You could write some VBA to run an append query for each of your tables to consolidate it all onto one.

 

[guitarist13]

I am happy to say that is beyond my skill set.

The VBA I mentioned before I took from a website, but thanks for the suggestion.

 

[wiklendt]

Any suggestions would be great. Perhaps I have missed something obvious.

yup. you missed the two or so posts that already told you (edit: suggested to you) to use a union query.

 

[guitarist13]

Indeed, but I don't fancy writing out a Union Query for 50+ Tables, whose names will likely change in the future. I am trying to avoid specifying Table names if I am going to be using all the Tables anyway.

Hope that makes sense.

 

[pbaldy]

I'm not sure what you're after then. You say you're happy to not be able to code, so code-based solutions are out. Access is made to deal with normalized data, so you are pretty much doomed to work around that data structure if you leave it that way. There is no magic "get me all the data from any tables you find" SQL statement. I would start the first table of the UNION query, then copy/paste for the others, where you should only have to change the table name in the FROM clause.

 

[georgedwilkinson]

You could write VBA that will create a union query for you. That seems like even MORE work than just biting the bullet and writing the union query in the first place.

Access will do a lot of things but not doing anything and expecting this particular result is beyond its (or any other environment's) abilities.

 

[Atomic Shrimp]

If the tables are all the same structure, writing the union query should involve not much more than a session of copying and pasting (with a tiny bit of editing to change table names). Then run a make-table query based on the union query's results, then use that freshly-made table forevermore afterwards.

 

[guitarist13]

Thanks everyone. I will have to think about this some more.

pbaldy, what do you mean by normalized data?

 

[DCrake]

In your access database go to Tools and Analyse then pick performance followed by Tables. I think you will get all the answers you need there about normalisation.

David

 

[pbaldy]

http://www.r937.com/Relational.html

http://en.wikipedia.org/wiki/Database_normalization