Access Multiple Queries Question 1

[alphil2001]

Hi, Ive searched through the forums and cant seem find anything that may help me.

I have over 30 seperate queries, Each query is the result of looking through another query and table and returning the last transaction date and transaction number and each query returns a different number of records from about 300 to 500 transactions. The seperate queries work well but I would like to combine them in one table, this is what the seperate queries produce:

Date Transaction
01/01/06 250
05/01/06 355
03/02/06 450

What I would like to know is, is it possible to combine all of the queries into one datagrid or table so they are listed side by side so I can view them all at the same time so it would look something like this:

Date1 Transaction1 Date2 Transaction2 etc
01/01/06 250 03/01/06 150
05/01/06 355 07/01/06 225
03/02/06 450 06/02/06 578


I would also like to know if it is possible to have a running (cumulative)total next to each of the transaction columns showing the Last transaction added onto the next transaction.

eg
Date Transaction Running Total
01/01/06 250 250
05/01/06 355 610
03/02/06 450 1060

Thanks very much for your help, I hope you will be able to help me, or suggest an easier was round of doing this
ALPHIL2001

 

[alphil2001]

Sorry Alphil2001 here again

Forgot to mention that the queries were Union SQL queries which pull information from one table and sums the total number of transactions on given dates
Thanks
hope you can help
Alphil2001

 

[lespaul]

What I would like to know is, is it possible to combine all of the queries into one datagrid or table so they are listed side by side so I can view them all at the same time so it would look something like this:

Date1 Transaction1 Date2 Transaction2 etc
01/01/06 250 03/01/06 150
05/01/06 355 07/01/06 225
03/02/06 450 06/02/06 578

where do these other dates (Date2) come from?

It's much easier for us to help you if you will describe the raw data in the tables involved and what you want the final results to be:
[tt]
Table1
PK field1 Field2
data data data
data data data
data data data

Table2
PK FK Field3
data data data
data data data
data data data

Results
T1.PK T1.Field2 T2.FK T2.Field3
data data data data
data data data data
data data data data
data data data data

[/tt]
and show how any calculations should be done. That's really helpful.

Thanks,

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[alphil2001]

Hi Leslie

Thanks for your quick response.

the table has a List of dates and offices as fields

ID ID Field
Date1 Date Of Transaction
Office1 Office Name
Type1 Type of Sale
Date2 Date Of Transaction
Office2 Office Name
Type2 Type of Sale
Date3 Date Of Transaction
Office3 Office Name
Type3 Type of Sale
Date4 Date Of Transaction
Office4 Office Name
Type4 Type of Sale
Date5 Date Of Transaction
Office5 Office Name
Type5 Type of Sale
Date6 Date Of Transaction
Office6 Office Name
Type6 Type of Sale

there are over 60 Offices

I record under the office fields the number of transactions for the office and the type of sale such as Desk, Chair etc sold by the office. I use individual union sql queries for each type to look through the table of all the offices and sum the transaction for each type which is shown as a list of dates and totals for that date of all the offices for that type of transaction.

the list the Sql query produces for each type of transaction such as chair table etc consists of fields

Date Date Field
Transaction Field (sum)


What I would like to produce is a Table or grid or Query that will combine all of the individual Lists from the SQL List produced by the query into one large list again showing

One date field
And the Transaction fields going across the top of the list with cumulative running totals

Date1 Date field
Transaction1 Sum of office totals for transactions
Running Total1 Cumulative running total transactions.
Date2 Date field
Transaction2 Sum of office totals for transactions
Running Total2 Cumulative running total transactions.
Date3 Date field
Transaction3 Sum of office totals for transactions
Running Total3 Cumulative running total transactions.


There are no calculations as all of the calculations have already been done in the individual sql queries, the only calculation I need to try and figure out with your help is a running total for each of the transaction fields

Thanks
Alphil2001

 

[lespaul]

The biggest problem is the lack of normalization in your tables. Having fields Date1, Date2, Date3 is a red flag that you haven't reached third normal form (see fundamentals document for more detailed information regarding normalization).

There are no calculations as all of the calculations have already been done in the individual sql queries, the only calculation I need to try and figure out with your help is a running total for each of the transaction fields

Unfortunately, without seeing EVERYTHING, I don't think there's anyway for me to help you do what you need.

What I would do is create a query that normalizes your data:

SELECT Office1 As Office, Date1 As TransactionDate, Type1 As TransactionType FROM TableName
UNION
SELECT Office2, Date2, Type2 FROM TableName
UNION
SELECT Office3, Date3, Type3 FROM TableName
etc.
....
SELECT Office6, Date6, Type3 FROM TableName

If you save this (qryNormal) and then use this as the source of the rest of your queries it will be much easier to report. I'm confused though, which of these fields holds the Transaction Amount?




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[alphil2001]

Thanks very much for you help LesPaul

Ive figured it out and it is doing what I want it to do.

Kind regards
Alphil2001 (star well earned)