Need Help, UNION Query / Crosstable?

[Walshie]

Hi,

I have data which is set out as follows:

PartNumber | Sales Wk52 | Sales Wk51 | Sales Wk50 etc..
12345 | 122 | 150 | 179

I need the data in the following format so I can compare it:

SalesWeek | Item Number | Usage
Sales Wk52 | 12345 | 122
Sales Wk51 | 12345 | 150
Sales Wk50 | 12345 | 179

I'm pretty good with VBA but fairly new to queries. Could anyone point me in the right direction? im not looking for someone to write everything for me just really confusing me this one.

Cheers
Chris

 

[spikepl]

You can use a union query, with one select statement for each week. http://office.microsoft.com/en-us/a...eries-by-using-a-union-query-HA010206109.aspx

Your data suck in terms of structure, and it would be better to stuff it into a normalized table first. Depends what else you want to do with it. I presume it is an import.

 

[Walshie]

Thanks for the help, not 100% happy with what i've had to do but it seems to be doing the job nicely. I've make two union Queries, "Wk 1 - 26" and "Wk 27-53" I've had to use two as there was too much data to have wk 1 - 53.

I've then had to make another union query to merge all the two into one.

It is working fine now but can you see any problems i may encounter?

As for the data i agree its supplied in a really poor format but its all I am supplied from an external source, they don't have the capability to change this either so I have to just work with what I have.

Thanks again!

Cheers
Chris