Report tied to multiple queries?

[Wendy]

Hi, I'm working on what seemed a simple database, but I can't get started. I
have table with only 3 fields: Account number, account description and
amount. I want to create a report that list all accounts pertaining to a
specific group (recruitment), another line with all clinic account, another
with faculty accounts, etc... I was thinking of doing multiple queries that
pulled teh accounts on each sections, and have multple reports. How do I
then tie a report to these multiple queries. I don't know if this can be
done or if there is an easier way. My report should look as this:
Accounts Amount
Recruitment 130,000
Clinic 420,000
......

 

[KARL DEWEY]

One query should do it but how do you distinguish a Recruitment account from
a Clinic account? Are the account numbers a different sequence?

 

[mscertified]

Sounds like you need to GROUP by the Account Type.
This can be done in your SQL query or within the report via the 'sorting and
grouping' option.

-Dorian

 

[Wendy]

Thanks. That's what I wanted to use the query to separate these account.
Fro example I know acct 010001 and 01002 are recruitment, so I wanted to do a
query of acct like "01001" or "01002" But this only works if I do many
quesries, one for each acct type. The table that I have does not have
account type, only acct number, and amount. Someone mentioned grouping by
each type of account, but that would requiere adding a different "type of
acct" field on the table and that defeats the purpose of what I'm trying to
do.

Do you know how I can work tih one query and creatig different groups for
each set of account?

 

[Wendy]

Thanks. This would require adding a field "Account type" and the table I
download does not have this, if I have to do this manually every month, it
would defeat the purpose of teh report. I'm working with only Account number
and Amount fields. I have another table of what each account type is.
Should I do a query based on the two tables?

 

[KARL DEWEY]

You can do several things. In your query add an output calculated field
like this --
Account_Type: IIF(Left([Account number], 2) = "01","Recruitment",
IIF(Left([Account number], 2) = "02","Clinic", "Misc"))

 

[LauriS]

Wendy,

Create a new table that has the following fields:

Account Type
Account Number

In that table you will have one record for each different account number and
it will list the type of that number:

Acct No Acct Type
01001 Recruitment
01002 Recruitment
01003 Clinic

The you can link this table to your main table by the Account Number. Do a
totals query, Group By the Account Type and Sum the Amount.

If the account numbers can change you will need to check your downloaded
table each time to be sure you have all the account numbers accounted for.

To do that you would do a Select query using both tables, change the join
type to pull ALL the records from the downloaded table even if there isn't a
match, show the Account Number (from the downloaded table) and and the
Account Type (from your new table) and put in the condition Is Null for the
Account Type.

This will give you any account numbers where the Account Type field is blank
- meaning there wasn't a matching record in the new table for that account
number.

Hope this helps!

Lauri S.