Using data from 2 tables with different data levels

[petejigsaw]

Hi

I'm working on a report to summarise sales and headcount (footfall) in a retail environment.

The report has 2 table sources - one for the sale data, and one for the headcount data. Sample data below:

Sales Data:

Date Store ID Trans ID Product Value
01/02/2010 10 1 ABC 125.00
01/02/2010 10 2 DEF 50.00
01/02/2010 20 3 GFH 75.00
02/02/2010 10 4 HGJ 100.00
02/02/2010 10 5 KJL 150.00
02/02/2010 20 6 KIL 500.00


Headcount Data:

Date Store ID Headcount
01/02/2010 10 63
01/02/2010 20 86
02/02/2010 10 73
02/02/2010 20 54


I've linked the two tables together on the DATE and STORE ID fields, as these are the only two fields that are common.

However, a simple report with a summary for each store on each day then gives:

Date Store ID Headcount Trans ID Value
01/02/2010 10 63 1 125.00
01/02/2010 10 63 2 50.00

01/02/2010 10 126 175.00

01/02/2010 20 86 3 75.00

01/02/2010 20 86 75.00

02/02/2010 10 73 4 100.00
02/02/2010 10 73 5 150.00

02/02/2010 10 146 250.00

02/02/2010 20 54 6 500.00

02/02/2010 20 54 500.00

As can be seen, the headcount repeats for every transaction at the same Store ID on each Date, and when summarised becomes misleading. This makes summarising and evaluating the data very difficult.

What I'm after is some guidance on how to use the data in a way that allows the Headcount data to be summarised at various group levels (Date, Store ID etc.).

Thanks in anticipation of any valuable assistance.

 

[Madawc]

If you're wanting to report the data one way and total it another, try Crosstab.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[petejigsaw]

Thanks Madawc

I've had a look at using a crosstab report but don't think its the right format for this data.

We have a raft of sales reports which are based on just the Sales Data table, and simply want to add the Headcount data to it. Its just that I can't work out how to do that in the simplest way so as to allow summaries to be included etc.

 

[petejigsaw]

I think I've now worked it out - a combination of some running totals and formulas seems to give me data presented as I need.

Thanks