'Duplicate' records returned in query.

[Guest]

I have searched this discussion group to no avail. I am trying to bring data
from two different tables together to produce a report. The first table is
my billing records; my second table is my expenses table. The problem I am
having is that if there is more than one expense, my billings are 'repeated'
in my query. Now for the example:

Query results:
Expense 1 Billing 1
Expense 2 Billing 1
Expense 3 Billing 2
Expense 4 Billing 3
Expense 5 Billing 4
Expense 6 Billing 4

This query is being used as the record source for a report to produce
invoices. In a perfect world, I'd like to my report to display my Expenses
per Billing:
Billing 1
Expense 1
Expense 2
Billing 2
Expense 3
Billing 3
Expense 4
Billing 4
Expense 5
Expense 6

Any and all suggestions would be very helpful.

 

[BruceM]

If you are saying that each billing may contain multiple related expenses,
then you need a one-to-many relationship between Billing and Espenses.

tblBilling
BillingID (primary key, or PK)
JobNumber
Other fields specific to billing

tblExpenses
ExpenseID (PK)
BillingID (foreign key, or FK)
Description
Other fields specific to the expense

Create a one-to-many relationship between the two BillingID fields. A
form/subform setup will let you add one or more expenses for each billing.

I'm not completely certain I understand your meaning, so I won't go into any
more detail for now. If I understand the situation correctly, check Help
for information about primary keys and relationships (and anything else you
care to investigate), and post back with any further questions. If I have
misunderstood, please clarify.

 

[Guest]

Thanks for the response. I've already done all of that. The question was
more relating to when I do a query that had both of those tables, the itmes
from the billing side would be shown more than once if there was more than
one expense. Since this query was the record source for my report, it was
'double' billing the billing items. I have since modified my report with
groups (and put my billing in the group header) with my expenses in the
detail section and it is working nicely. I almost always find a solution
with access, even if it is not straightforward, intuitive, or simple! Thanks
for the reply.

 

[BruceM]

I'm glad to know it worked, but I wonder why you didn't use a subreport just
as you used a subform.

 

[Guest]

I didnt' use a subform simply because I want to keep my expenses with each
billable item, as per my first psot. If this is possible using subreports,
great!, let me know.

The only problem I've encountered now is this: since my expense section can
only be placed below by billing section (since by billable items are in a
header section (per their ID), and my expense in the detail section) there is
quite a bit of of wasted space between the two as a result of the size of my
description field in my header section. Any ideas how I can avoid this?
Thanks for you help.