Duplicates in Query/report

[jwrnana]

How do I prevent duplicates from listing in a query? i.e. I want the date
of the order, customer account number, and total sales. If there is more
than one line item on the invoice, I get the customer name and account
number listed for each line item.

 

[jwrnana]

I changed the properties to Yes -- I did not notice any change. In this
query, there are many fields that I need. I need order date; customer
number, address, phone,e-mail; ship to and bill to addresses;units ordered,
product description, unit price, total line item price; delivering dealer;
invoice date; payment date.

Can I get unit price, quantity and total line item price as well as a field
for Total Invoice amount? Can I get the payment amount listed only once per
invoice in lieu of once per line item -- not one each line item (tractor =
$4000.00, mower $2000.00 -- customer paid $6000.00 and my query shows $6000
paid on the tractor and $6000 paid on the mower -- not just $6000). Same
for all fields. The only fields that would have multiple entries in it
would be the units ordered, product, unit price and line item total.

thank you

 

[Joan Wild]

I changed the properties to Yes -- I did not notice any change. In this
query, there are many fields that I need. I need order date; customer
number, address, phone,e-mail; ship to and bill to addresses;units ordered,
product description, unit price, total line item price; delivering dealer;
invoice date; payment date.

Can I get unit price, quantity and total line item price as well as a field
for Total Invoice amount? Can I get the payment amount listed only once per
invoice in lieu of once per line item -- not one each line item (tractor =
$4000.00, mower $2000.00 -- customer paid $6000.00 and my query shows $6000
paid on the tractor and $6000 paid on the mower -- not just $6000). Same
for all fields. The only fields that would have multiple entries in it
would be the units ordered, product, unit price and line item total.

You really can't do this in a query, but you can on your report.

I wouldn't put the Total Invoice amount in the query at all. Just include a
textbox on your report that calculates this.

You can create a report group based on the Customer number (and include
their address, phone, email) in it.

I'd also create a report group for the order that has the order date, ship
to and bill to addresses, invoice date and payment date.

The detail section would have the invoice details, and the the order group
footer you'l put the textbox with a control source of
=Sum([line item total])

 

[jwrnana]

Can I have all of this on one report and then can that report be sent to
Excel for me to email to someone? Joy Rose

I changed the properties to Yes -- I did not notice any change. In this
query, there are many fields that I need. I need order date; customer
number, address, phone,e-mail; ship to and bill to addresses;units ordered,
product description, unit price, total line item price; delivering dealer;
invoice date; payment date.

Can I get unit price, quantity and total line item price as well as a field
for Total Invoice amount? Can I get the payment amount listed only once per
invoice in lieu of once per line item -- not one each line item (tractor =
$4000.00, mower $2000.00 -- customer paid $6000.00 and my query shows $6000
paid on the tractor and $6000 paid on the mower -- not just $6000). Same
for all fields. The only fields that would have multiple entries in it
would be the units ordered, product, unit price and line item total.

You really can't do this in a query, but you can on your report.

I wouldn't put the Total Invoice amount in the query at all. Just include a
textbox on your report that calculates this.

You can create a report group based on the Customer number (and include
their address, phone, email) in it.

I'd also create a report group for the order that has the order date, ship
to and bill to addresses, invoice date and payment date.

The detail section would have the invoice details, and the the order group
footer you'l put the textbox with a control source of
=Sum([line item total])

 

[Joan Wild]

Yes, it can be in one report, and yes to can export to Excel using Tools,
Office Links, however it usually doesn't export very nicely.

You might want to consider sending the report as RTF format (still not
perfect), or as a snapshot. The recipient of the report can view it using
the snapshot viewer available for download from microsoft.com