Duplicating lines in an Invoice Report

[GordonEdinburgh]

Hello,

I am sure someone will have a simple answer to this.

I am building a simple invoicing tool. It records the clients details in a
registration table, with a uniqe issues autonumber account number (with a
primary key). The second table contains a list of products,

The form contains 2 parts

1) To list the customers and type in a customer orders number.
2) Sub-form to allow the customer to buy many products.

All works fairly well. Except in the sub form, there is a field that allows
some notes to be typed into a box. Again that works ok. (We type dates into
this field)

But if I add a second line with the same probduct and a different date, the
lines double themselves on the report. But the final information that gets
recorded onto a third accounts table is correct.

Here are some expamples of the sub-forms (not all the details, just the
problem part)

Buffet, ,10, £5:00....
Donughts, 27th April, 100, £0.15....
Donughts, 28th April, 150, £0.15...
Canapies, ,50, £1.00...

So far to good, when I press the button to create a report, it posts all the
correct informtion to the apporiate table witht the correct amounts.

How the report will come out like this.

The field where the date gets added is called the date of service field.

Buffet, ,10, £5:00....
Donughts, 27th April, 100, £0.15...
Donughts, 28th April, 100, £0.15...
Donughts, 27th April, 150, £0.15...
Donughts, 28th April, 150, £0.15...
Canapies, ,50, £1.00...

So wheneven some extra text or dates are typed in the sub-form, the report
seems to double the lines and give me the wrong invoice amount, and yet the
correct amount will have been posted to the approriate table.

The problems seem when the dates are added. (What the customer wants).

Many thanks for your help

 

[Marshall Barton]

I am building a simple invoicing tool. It records the clients details in a
registration table, with a uniqe issues autonumber account number (with a
primary key). The second table contains a list of products,

The form contains 2 parts

1) To list the customers and type in a customer orders number.
2) Sub-form to allow the customer to buy many products.

All works fairly well. Except in the sub form, there is a field that allows
some notes to be typed into a box. Again that works ok. (We type dates into
this field)

But if I add a second line with the same probduct and a different date, the
lines double themselves on the report. But the final information that gets
recorded onto a third accounts table is correct.

Here are some expamples of the sub-forms (not all the details, just the
problem part)

Buffet, ,10, £5:00....
Donughts, 27th April, 100, £0.15....
Donughts, 28th April, 150, £0.15...
Canapies, ,50, £1.00...

So far to good, when I press the button to create a report, it posts all the
correct informtion to the apporiate table witht the correct amounts.

How the report will come out like this.

The field where the date gets added is called the date of service field.

Buffet, ,10, £5:00....
Donughts, 27th April, 100, £0.15...
Donughts, 28th April, 100, £0.15...
Donughts, 27th April, 150, £0.15...
Donughts, 28th April, 150, £0.15...
Canapies, ,50, £1.00...

So wheneven some extra text or dates are typed in the sub-form, the report
seems to double the lines and give me the wrong invoice amount, and yet the
correct amount will have been posted to the approriate table.

The problems seem when the dates are added. (What the customer wants).

I will bet that the problem is in the report's record source
query. For the moment, forget about the report and just run
its record source query by itself to see what data records
it returns. I think you will find that the query is joining
tables inappropriately.

 

[GordonEdinburgh]

I am building a simple invoicing tool. It records the clients details in a
registration table, with a uniqe issues autonumber account number (with a

[quoted text clipped - 40 lines]

The problems seem when the dates are added. (What the customer wants).

I will bet that the problem is in the report's record source
query. For the moment, forget about the report and just run
its record source query by itself to see what data records
it returns. I think you will find that the query is joining
tables inappropriately.

Hello Marshall,

Thank you for replying....I have tested the queries and all work fine. All
the informaiton goes through the whole process and even records the final
figuers onto a accounts table with the correct figures....its odd that it is
just the final report which creates these duplicate lines.

I don't know enough about reports to sort this one out.

Regards

Gordon

 

[Marshall Barton]

I am building a simple invoicing tool. It records the clients details in a
registration table, with a uniqe issues autonumber account number (with a

[quoted text clipped - 40 lines]

The problems seem when the dates are added. (What the customer wants).

I will bet that the problem is in the report's record source
query. For the moment, forget about the report and just run
its record source query by itself to see what data records
it returns. I think you will find that the query is joining
tables inappropriately.

Thank you for replying....I have tested the queries and all work fine. All
the informaiton goes through the whole process and even records the final
figuers onto a accounts table with the correct figures....its odd that it is
just the final report which creates these duplicate lines.

I don't know enough about reports to sort this one out.

Please post a Copy/Paste of the report record source query's
SQL statement, and we'll see if anyone can spot something.

 

[GordonEdinburgh]

[quoted text clipped - 14 lines]

I don't know enough about reports to sort this one out.

Please post a Copy/Paste of the report record source query's
SQL statement, and we'll see if anyone can spot something.

SELECT [tbl - orders - sales].[Order ID], [tbl - orders - details].[Invoiced
Raised], [tbl - orders].[Order date], [tbl - registration].[Account number],
[tbl - registration].[First Name], [tbl - registration].[Last Name], [tbl -
registration].[Company Name], [tbl - registration].[Address 1], [tbl -
registration].[Address 2], [tbl - registration].Town, [tbl - registration].
[Post Code], [tbl - products and services].[Product code], [tbl - products
and services].Description, [tbl - products and services].Classification, [tbl
- orders - sales].Qty, [tbl - orders - sales].Cost, [tbl - orders - sales].
[Sub total], [tbl - products and services].[VAT Rate], [tbl - orders - sales].
VAT, [tbl - orders - sales].[Total price], [tbl - orders].[Order ID], [tbl -
orders].[Customer order number], [tbl - orders - details].[Dates for service]
FROM [tbl - registration] INNER JOIN (([tbl - orders] INNER JOIN [tbl -
orders - details] ON [tbl - orders].[Order ID] = [tbl - orders - details].
[Order ID]) INNER JOIN ([tbl - products and services] INNER JOIN [tbl -
orders - sales] ON [tbl - products and services].[Product code] = [tbl -
orders - sales].[Product code]) ON ([tbl - products and services].[Product
code] = [tbl - orders - details].[Product code]) AND ([tbl - orders].[Order
ID] = [tbl - orders - sales].[Order ID])) ON [tbl - registration].[Account
number] = [tbl - orders].Customer
GROUP BY [tbl - orders - sales].[Order ID], [tbl - orders - details].
[Invoiced Raised], [tbl - orders].[Order date], [tbl - registration].[Account
number], [tbl - registration].[First Name], [tbl - registration].[Last Name],
[tbl - registration].[Company Name], [tbl - registration].[Address 1], [tbl -
registration].[Address 2], [tbl - registration].Town, [tbl - registration].
[Post Code], [tbl - products and services].[Product code], [tbl - products
and services].Description, [tbl - products and services].Classification, [tbl
- orders - sales].Qty, [tbl - orders - sales].Cost, [tbl - orders - sales].
[Sub total], [tbl - products and services].[VAT Rate], [tbl - orders - sales].
VAT, [tbl - orders - sales].[Total price], [tbl - orders].[Order ID], [tbl -
orders].[Customer order number], [tbl - orders - details].[Dates for service]
HAVING ((([tbl - orders - sales].[Order ID])=[Forms]![Frm - Order screen]!
[Order ID]) AND (([tbl - orders - details].[Invoiced Raised])=No));

 

[Marshall Barton]

I am building a simple invoicing tool. It records the clients details in a
registration table, with a uniqe issues autonumber account number (with a

[quoted text clipped - 14 lines]

I don't know enough about reports to sort this one out.

Please post a Copy/Paste of the report record source query's
SQL statement, and we'll see if anyone can spot something.

Reformatting your query to make it a little more readable:

SELECT S.[Order ID], D.[Invoiced Raised], O.[Order date],
R.[Account number], R.[First Name], R.[Last Name],
R.[Company Name], R.[Address 1], R.[Address 2],
R.Town, R.[Post Code], PS.[Product code],
PS.Description, PS.Classification, S.Qty, S.Cost,
S.[Sub total], PS.[VAT Rate], S.VAT, S.[Total price],
O.[Order ID], O.[Customer order number],
D.[Dates for service]

FROM [tbl - registration] As R
INNER JOIN (([tbl - orders] As O
INNER JOIN [tbl - orders - details] As D
ON O.[Order ID] = D.[Order ID])
INNER JOIN ([tbl - products and services] As PS
INNER JOIN [tbl - orders - sales] As S
ON PS.[Product code] = S.[Product code])
ON PS.[Product code] = D.[Product code]
AND O.[Order ID] = S.[Order ID])
ON R.[Account number] = O.Customer

GROUP BY S.[Order ID], D.[Invoiced Raised], O.[Order date],
R.[Account number], R.[First Name], R].[Last Name],
R.[Company Name], R.[Address 1], R.[Address 2],
R.Town, R.[Post Code], PS.[Product code],
PS.Description, PS.Classification, S.Qty, S.Cost,
S.[Sub total], PS.[VAT Rate], S.VAT, S.[Total price],
O.[Order ID], O.[Customer order number],
D.[Dates for service]

WHERE (S.[Order ID] =
[Forms]![Frm - Order screen]![Order ID])
AND (D.[Invoiced Raised] = No);

If you run that query, I'm sure that you will see that the
order details are "duplicated" when there is more than one
product and service (or sales) record in an order.

I can't tell for sure, but it looks like the products and
services (or sales) can have multiple records indepently of
the order details.

You need to analyze how these tables relate and adjust the
Joins in the query (or remove one or more tables and use
subreports) before worring about what the report looks like.

 

[GordonEdinburgh via AccessMonster.com]

Hello Marshall,

Firstly, I do appreciate you looking at this.

If I run it all through, without raising hte invoice, it all work perfectly
and even with the invoice report added added back in, it adds the correct
values to the approoraite field. Just the invoice report itself is wrong

the query seems to fall over, qry-final invoice (which the report bases its
self up) will double up the line when text is added to the dates of service
field.

Eg.(if you add this into the invoice screen)

Donoughts, 12th April, 100, 0.15..........
Donoughts, 14th April, 150, 0.15..........

What you end up withi is this.

Donoughts, 12th April, 100, 0.15..........
Donoughts, 14th April, 100, 0.15..........
Donoughts, 12th April, 150, 0.15..........
Donoughts, 14th April, 150, 0.15..........

Just when you add dates to the dates of service field.

Regard,

Gordon....

 

[GordonEdinburgh via AccessMonster.com]

Marshall,

I have just been sent the answer to my problem

To fix the problem as it stands you need to create a join in the query [qry -
final invoice] between [dates of service] in the tables [tbl - orders -
details] and [tbl orders - sales].

It was a normalization problem.

Thank you for all your help and looking at this for me.

Regards

Gordon