Limiting report data in the report and not in the query

[Guest]

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

 

[krissco]

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

 

[Guest]

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

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

 

[Guest]

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

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

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

 

[krissco]

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