If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using multiple tables/queries in one report
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 |
#2
|
|||
|
|||
Using multiple tables/queries in one report
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 " 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 |
#3
|
|||
|
|||
Using multiple tables/queries in one report
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 " wrote in message news:2dac101c46b60$3795e7a0 ... 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 . |
#4
|
|||
|
|||
Using multiple tables/queries in one report
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 " 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 " wrote in message news:2dac101c46b60$3795e7a0 ... 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 . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Concatenating multiple values into a single field on a report | Kevin | Running & Setting Up Queries | 8 | July 16th, 2004 03:31 PM |
Label | SRIT | General Discussion | 2 | June 22nd, 2004 09:42 PM |
How to print an Access Report multiple times, w/ different headers | Dave Corun | Setting Up & Running Reports | 3 | June 2nd, 2004 02:31 AM |
Multiple instances of a report on the same page | Chris | Using Forms | 6 | June 1st, 2004 04:20 PM |
multiple recourd sources for a report | carlee | Setting Up & Running Reports | 1 | May 28th, 2004 07:47 AM |