Cross Tab & relationships

[tomburns1987]

Hi,



I have an exel file that i import into Access which stores records for a cash flow analysis.

I want to change the layout of the records to get it into a certain way to create a report on the cross tab.

It currently looks something like this; (with lots more records and shops names)

Shop Name Month Net Sales Rent Rates Expenses Stock Movement
Bath October £2000 (£100) (£100) (£150) (£200) £1450
Bath November £2200 (£100) (£100) (£200) (£300) £1500


I would like it to look like this;

Shop Name Income/Expenditure October November Month(3) Month(4)......

Bath Net sales £2000 £2200
Bath Rent (£100) (£100)
Bath Rates (£100) (£100)
Bath Expenses (£150) (£200)
Bath Stock (£200) (£300)
Bath Movement £1450 £1500



I don't think its working because i can only select one field as the 'Value' part of the cross tab query. I can get the layout to work properly but the figures are not populating as i can only select one of the expenses as an option.

Is this because my tables are incorrelty linked or poor set up maybe? Or have i taken the wrong approach to this?

Any help will be greatly appreciated

[June7]

Problem is the original data is not normalized structure. Needs to be:

ShopName | Month | Category | Amount

How many categories (Rent, Stock, etc) are there? If not more than 50, a UNION query can manipulate the data into normalized structure.

Then that structure can be pivoted by CROSSTAB.

[tomburns1987]

Right ok. There about 12 categories. I can change this in the Excel file that imports.

Thanks