Querying Monthly Data Tables into one (1 Viewer)

ToffeeMark · May 15, 2017

Hi, This might be a really basic question, I don't do much in SQL, tend to mainly use the design mode.

I get verylarge monthly data tables in which every row has a period identifier e.g. 201705.

All the monthly tables are separate.201507, 201506 etc

I've linked them into my database.

Can a query pull out data from all the different month tables and perform a make table (or select) into one table?

Or do I have to query each one separately and then append together. Or append all the months into one humongous table and then query that...

Any help on how you would tackle this problem or what I should read up on would be gratefully received.

Thanks

Mark

moke123 · May 15, 2017

have you considered a union query?
is this a one time effort to consolidate data?

ToffeeMark · May 15, 2017

Thanks for the reply.
I haven't considered a union query, Its not a query type I ever really use or understand to be honest.
It would probably be a regular need to add monthly tables and interrogate the tables for different questions. e.g. pull all the data for one customer out of each of the month table, or all the data for one product etc. I was thinking is there away to link the tables or tell access that they're monthly sales tables. But the whole union, linking is a bit of a grey area for me.

Galaxiom · May 15, 2017

Depending on their original format, when you linked them you might have been able to use the append option on the second and subsequent imports to put them into the same table.

Otherwise the union is simple.

Code:

SELECT * FROM [201506]
UNION ALL
SELECT * FROM [201507]
UNION ALL
etc

ToffeeMark · May 15, 2017

Thanks for the reply, I've not used a union query before but I'll have a play and do some homework on it.

ToffeeMark · May 17, 2017

Hi I don't know hoe to mark this thread as Solved but just to say the Union query did the job, thanks!

Gasman · May 17, 2017

ToffeeMark said:
Hi I don't know hoe to mark this thread as Solved but just to say the Union query did the job, thanks!

Click on Thread Tools as the top of the post. There is an option to mark as solved if you are the o/p.

moke123 · May 17, 2017

Although you didnt say if this was a one-off solution, it appears you will be adding months. I would be inclined to append this data to one table or you will have to keep adding each month to the union query.

ToffeeMark · May 17, 2017

moke123 said:
Although you didnt say if this was a one-off solution, it appears you will be adding months. I would be inclined to append this data to one table or you will have to keep adding each month to the union query.

Thanks, It is probably going to be ongoing but some of the tables can have a million rows so I'm not sure if I can append them all together, I could probably do that for the smaller data sets but I'm not even loading the data into the database at the moment, I'm just linking the tables, I don't know if an append query would draw all the data into access and then make it too big (24gb?)

It probably isn't that big a problem to add to the Union query each month but then I've only tried it on a relatively small dataset, I guess the bigger ones might cause the laptop to meltdown...

Querying Monthly Data Tables into one (1 Viewer)

ToffeeMark

New member

moke123

AWF VIP

ToffeeMark

New member

Galaxiom

Super Moderator

ToffeeMark

New member

ToffeeMark

New member

Gasman

Enthusiastic Amateur

moke123

AWF VIP

ToffeeMark

New member

Similar threads

Users who are viewing this thread