Hi I need in setting up tables as follows


The fields I use are Loation1,2,3 etc..
Revenues details
Revenue Totals
Period.

I need to run reports which tells how much revenue is generated on a particular day or Month to date, or Year to date from a particular location.

Thanks,
Ven

These two tables will store the data.

tblLocation
LocationID (autonumber primary key)
LocationName

tblTransaction
TransactionID (autonumber primary key)
LocationID (foreign key to tblLocation)
TransactionDate
TransactionAmount

You can use a query based on joining these two tables as the source for a crosstab query if you want to see the amounts as you have shown them. Since a crosstab can only accumulate a single field at a time, you'll need three crosstabs, one for today, one for month to date, and the third for year to date. You would then join the three crosstabs to produce the recordset for the final report.

Hi Pat Hartman,

I will try this.

Thanks,
Ven.