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
|
|||
|
|||
Limiting report data in the report and not in the query
Hi,
I'm still relatively new working with Access and on top of that I've inherited the managment of someone else's database. I am trying to generate a report for invoices for a contract. We have several contracts, each with a unique contract number (contract) and each contract receives several invoices (invoice num). Each invoice has costs assigned to different concepts, i.e. admin costs, labor, etc, which are identified by codes (costcode). There are several cost codes per invoice, and several invoices per contract. The problem is: when I receive a new invoice I want to print out a report which shows both the total invoiced for the contact as well as the cost code details. The total for the contract has to read all of the invoices, but the cost code should be exclusive to the current invoice which appears in the invoice form. I can't seem to understand how to place a limit on the invoice number field ([invoice num = [Forms]![Form Invoices General]![invoice num) so I can display the cost code information, and then remove thhis filter in the footer of the report so that it shows the sum of all invoices in the contract. I am tempted to use a sub-report, but I would rather not if its not necessary. Can anyone give me some advice? Thanks for the help., Thomas |
#2
|
|||
|
|||
Limiting report data in the report and not in the query
On Aug 8, 7:08 am, Thomas wrote:
Hi, I'm still relatively new working with Access and on top of that I've inherited the managment of someone else's database. I am trying to generate a report for invoices for a contract. We have several contracts, each with a unique contract number (contract) and each contract receives several invoices (invoice num). Each invoice has costs assigned to different concepts, i.e. admin costs, labor, etc, which are identified by codes (costcode). There are several cost codes per invoice, and several invoices per contract. The problem is: when I receive a new invoice I want to print out a report which shows both the total invoiced for the contact as well as the cost code details. The total for the contract has to read all of the invoices, but the cost code should be exclusive to the current invoice which appears in the invoice form. I can't seem to understand how to place a limit on the invoice number field ([invoice num = [Forms]![Form Invoices General]![invoice num) so I can display the cost code information, and then remove thhis filter in the footer of the report so that it shows the sum of all invoices in the contract. I am tempted to use a sub-report, but I would rather not if its not necessary. Can anyone give me some advice? Thanks for the help., Thomas I would do this using queries. 1. Create this query (change the names of the fields/tables where necessary). I assume since each invoice has "costs" that an invoice has 1-m lines, and each line has its own cost/cost code. qryContractTotals: select contract, sum(Costs) as contractTotal from invoice, invoiceline where invoice.invoicenumber = invoiceline.invoicenumber group by contract 2. It sounds like you already have this query (or something similar) as the record source of your report: qryInvoiceDetails: select * from invoice, invoiceline where invoice.invoicenumber = invoiceline.invoicenumber 3. Use this query to report on: select * from qryInvoiceDetails, qryContractTotals where qryInvoiceDetails.contract = qryContractTotals.contract and invoicenumber = forms!blah!blah -Kris |
#3
|
|||
|
|||
Limiting report data in the report and not in the query
Thanks, Kris, I'll give it a shot. I'm happy you didn't confirm my fear
that it has to be done using a subreport. Thomas "krissco" wrote: On Aug 8, 7:08 am, Thomas wrote: Hi, I'm still relatively new working with Access and on top of that I've inherited the managment of someone else's database. I am trying to generate a report for invoices for a contract. We have several contracts, each with a unique contract number (contract) and each contract receives several invoices (invoice num). Each invoice has costs assigned to different concepts, i.e. admin costs, labor, etc, which are identified by codes (costcode). There are several cost codes per invoice, and several invoices per contract. The problem is: when I receive a new invoice I want to print out a report which shows both the total invoiced for the contact as well as the cost code details. The total for the contract has to read all of the invoices, but the cost code should be exclusive to the current invoice which appears in the invoice form. I can't seem to understand how to place a limit on the invoice number field ([invoice num = [Forms]![Form Invoices General]![invoice num) so I can display the cost code information, and then remove thhis filter in the footer of the report so that it shows the sum of all invoices in the contract. I am tempted to use a sub-report, but I would rather not if its not necessary. Can anyone give me some advice? Thanks for the help., Thomas I would do this using queries. 1. Create this query (change the names of the fields/tables where necessary). I assume since each invoice has "costs" that an invoice has 1-m lines, and each line has its own cost/cost code. qryContractTotals: select contract, sum(Costs) as contractTotal from invoice, invoiceline where invoice.invoicenumber = invoiceline.invoicenumber group by contract 2. It sounds like you already have this query (or something similar) as the record source of your report: qryInvoiceDetails: select * from invoice, invoiceline where invoice.invoicenumber = invoiceline.invoicenumber 3. Use this query to report on: select * from qryInvoiceDetails, qryContractTotals where qryInvoiceDetails.contract = qryContractTotals.contract and invoicenumber = forms!blah!blah -Kris |
#4
|
|||
|
|||
Limiting report data in the report and not in the query
Kris,
Thanks again for the help, I was able to design the query based on the examples you provided. Additionally, after searching through several forums, I have determined that the database is poorly structured and needs to be completly re-done. This was one of the problems, I think, having the data spread all over god's creation. So I'm going to start from scratch. Wish me luck, and thanks again. Regards, Thomas "Thomas" wrote: Thanks, Kris, I'll give it a shot. I'm happy you didn't confirm my fear that it has to be done using a subreport. Thomas "krissco" wrote: On Aug 8, 7:08 am, Thomas wrote: Hi, I'm still relatively new working with Access and on top of that I've inherited the managment of someone else's database. I am trying to generate a report for invoices for a contract. We have several contracts, each with a unique contract number (contract) and each contract receives several invoices (invoice num). Each invoice has costs assigned to different concepts, i.e. admin costs, labor, etc, which are identified by codes (costcode). There are several cost codes per invoice, and several invoices per contract. The problem is: when I receive a new invoice I want to print out a report which shows both the total invoiced for the contact as well as the cost code details. The total for the contract has to read all of the invoices, but the cost code should be exclusive to the current invoice which appears in the invoice form. I can't seem to understand how to place a limit on the invoice number field ([invoice num = [Forms]![Form Invoices General]![invoice num) so I can display the cost code information, and then remove thhis filter in the footer of the report so that it shows the sum of all invoices in the contract. I am tempted to use a sub-report, but I would rather not if its not necessary. Can anyone give me some advice? Thanks for the help., Thomas I would do this using queries. 1. Create this query (change the names of the fields/tables where necessary). I assume since each invoice has "costs" that an invoice has 1-m lines, and each line has its own cost/cost code. qryContractTotals: select contract, sum(Costs) as contractTotal from invoice, invoiceline where invoice.invoicenumber = invoiceline.invoicenumber group by contract 2. It sounds like you already have this query (or something similar) as the record source of your report: qryInvoiceDetails: select * from invoice, invoiceline where invoice.invoicenumber = invoiceline.invoicenumber 3. Use this query to report on: select * from qryInvoiceDetails, qryContractTotals where qryInvoiceDetails.contract = qryContractTotals.contract and invoicenumber = forms!blah!blah -Kris |
#5
|
|||
|
|||
Limiting report data in the report and not in the query
On Aug 9, 1:04 am, Thomas wrote:
Kris, Thanks again for the help, I was able to design the query based on the examples you provided. Additionally, after searching through several forums, I have determined that the database is poorly structured and needs to be completly re-done. This was one of the problems, I think, having the data spread all over god's creation. So I'm going to start from scratch. Wish me luck, and thanks again. Regards, Thomas You are most welcome. You know your table structure better than I do. Based on the information I have, it seems that your table structure is OK. I would guess (based on what I know) that you should have the following schema: Contract {ContractID, ContractName, other contract fields, . . .} Invoice {InvoiceID, ContractID, other invoice fields . . . } InvoiceLine {InvoiceID, LineNumber, CostCodeID, Amount, . . .} CostCode {CostCodeID, Description} Typical data may look like: Contract: ContractID ContractName . . . 1 Joe's Warehouse 2 Paco's Mansion Invoice: InvoiceID ContractID . . . 1 1 2 1 3 2 InvoiceLine: InvoiceID LineNumber CostCodeID Amount 1 1 1 499.99 1 2 1 100.00 1 3 2 64.99 2 1 2 600.22 2 2 1 400 .. . . CostCode: CostCodeID Description 1 Administration 2 Labor 3 Freight So, if your database looks anything like that, having the data spread all over is a good thing. If you are annoyed by having to reference tons of tables every time you want the data, save a query that includes all the tables, and reference the table (that way you won't have to re-write the joins each time). One warning with that though - it will be slower when you only want specific data (a single invoice). Read up on database normalization if you don't already know about it. -Kris |
Thread Tools | |
Display Modes | |
|
|