Pulling User Defined Field Values from Multiple tables.

[NicotineJones]

Hi,

I have database with monthly sales data tables within it.

These sales data tables are organised as follows.

Item code; Description; Country1; Country2; Country3..etc
1234; Item1; Sales Value; Sales Value; Sales Value ..etc

I have a union query that provides a full list of all the Item Codes, but also wanted to be able to query the sales values for those items by month for user defined countries.

for example my "ideal" output would look like the following

Item Code; Description; Month1; Month2; Month3.. etc

where the month relates to the same country across all relvent tables.

[Stewart Ross]

Hi. Your tables are not in any way in normalised form - there are repeating groups for countries and sales in each table. You will not be able to do any useful data analysis unless you resolve your design into a normalised form, removing all repeating groups into discrete tables and being careful to define attributes (such as the date of a sale) in such a way that you don't end up with multiple columns in the resultant tables.

There is a HowTo article on Database Normalisation and Table Structures linked here.

I can only stress that you really cannot make any progress until you have a design which is suitable - and what you have at present is not even in what is known as First Normal Form.

Scrap it and start again - it will save you a lot of work in the long run.

-Stewart

[NicotineJones]

Not the ideal answer i was hoping for (lots more work for me to do), but the link you provided explains it all perfectly.

Thanks