Using multiple tables/queries in one report

[cottageconsignments]

Hello,

I have three tables (Customers, Sales, Payouts) - they all
contain the same field - account number. I would like to
print one report that contains all the records from the
Sales table and all the records from the Payouts table but
for just one account number. I'm not sure where to begin.

Thanks! Mary Beth

 

[Rick B]

You need to biuld a query and add each of the three tales to your query. It
should automatically link the tables if you have relationships built. If
not, you will have to click on the account number field in the customertable
and drag it to the sales table. A link should be created. Then do the same
for the payouts table.

After that, drag whichever fields you'd like to include in your query/report
to the bottom section of your query design window.

If you wish for the user to specify which account when the report is run,
put something like the follwoing under the Account number field (note that
you only need to include it once from any of the above tables, and it does
not really matter which one)...

[EnterCustomerNumber]



You can run the query, or use it as the record source for a report.

Hope that helps.

Rick B


"(e-mail address removed)" <[email protected]>
wrote in message Hello,

I have three tables (Customers, Sales, Payouts) - they all
contain the same field - account number. I would like to
print one report that contains all the records from the
Sales table and all the records from the Payouts table but
for just one account number. I'm not sure where to begin.

Thanks! Mary Beth

 

[cottageconsignments]

The only problem is that the information in the Sales
table is completely unrelated to the Payouts table.

Here's my example:

[CustomersTable]
AcctNo Name Address OpenDate

[SalesTable]
AcctNo Item Price Quantity Payout

[PayoutsTable]
AcctNo AmountPaid Date PmtMethod


The report I would like should look like the following:

AcctNo = A1

Sales
------
1/1/04 Item1 $5.00 $2.50
1/5/04 Item2 $2.00 $1.00
2/2/04 Item3 $10.00 $5.00
2/10/04 Item4 $1.00 $.50

Payouts
-------
1/30/04 $3.50 Cash
2/28/04 $11.00 Cash

The information in Payouts is not directly related to
Sales because it's a record of someone picking up their
payout. So they really are two separate reports.

I don't even know if this is possible. If you have any
other ideas, I'd appreciate it.

Thanks!
Mary Beth

-----Original Message-----
You need to biuld a query and add each of the three tales to your query. It
should automatically link the tables if you have relationships built. If
not, you will have to click on the account number field in the customertable
and drag it to the sales table. A link should be created. Then do the same
for the payouts table.

After that, drag whichever fields you'd like to include in your query/report
to the bottom section of your query design window.

If you wish for the user to specify which account when the report is run,
put something like the follwoing under the Account number field (note that
you only need to include it once from any of the above tables, and it does
not really matter which one)...

[EnterCustomerNumber]



You can run the query, or use it as the record source for a report.

Hope that helps.

Rick B


"(e-mail address removed)"

 

[Rick B]

If I am reading this correctly, you would have two queries, one to pull
sales, one to pull payouts, then a third query which includes the two
previously-created queries. Your report(s) would be based off that third
query.

You could use subreports.

Rick B


"(e-mail address removed)" <[email protected]>
wrote in message The only problem is that the information in the Sales
table is completely unrelated to the Payouts table.

Here's my example:

[CustomersTable]
AcctNo Name Address OpenDate

[SalesTable]
AcctNo Item Price Quantity Payout

[PayoutsTable]
AcctNo AmountPaid Date PmtMethod


The report I would like should look like the following:

AcctNo = A1

Sales
------
1/1/04 Item1 $5.00 $2.50
1/5/04 Item2 $2.00 $1.00
2/2/04 Item3 $10.00 $5.00
2/10/04 Item4 $1.00 $.50

Payouts
-------
1/30/04 $3.50 Cash
2/28/04 $11.00 Cash

The information in Payouts is not directly related to
Sales because it's a record of someone picking up their
payout. So they really are two separate reports.

I don't even know if this is possible. If you have any
other ideas, I'd appreciate it.

Thanks!
Mary Beth

-----Original Message-----
You need to biuld a query and add each of the three tales to your query. It
should automatically link the tables if you have relationships built. If
not, you will have to click on the account number field in the customertable
and drag it to the sales table. A link should be created. Then do the same
for the payouts table.

After that, drag whichever fields you'd like to include in your query/report
to the bottom section of your query design window.

If you wish for the user to specify which account when the report is run,
put something like the follwoing under the Account number field (note that
you only need to include it once from any of the above tables, and it does
not really matter which one)...

[EnterCustomerNumber]



You can run the query, or use it as the record source for a report.

Hope that helps.

Rick B


"(e-mail address removed)"