Trouble with Duplicates

[bradlee27514]

Ok, I'll try my best to explain this clearly. I'm using Great Plains SQL tables. I have three tables:

Table 1 (RM30201) has a record of all invoices and what check amounts were applied to them.

Table 2 (SOP30200) is a sales history table, which lists invoices and there details, but not the information on the individual line items on the invoice. In other words, it would show the ship date, and document total, but not the different line items and there quantities, etc.

Table 3 (SOP30300) is a sales history line item table. It lists the details on the line items for each invoice.

My problem is that Table 1 is my starting point and sometimes has more than one record for an invoice. This is an accounting issue that cannot be overcome, sometimes we just get multiple checks for the same invoice.

Table 2 only has one record for each invoice but needs to be joined to Table 3 which will have a record for each item on its invoice. Because Table 1 has multiple records I am getting duplicates of the sop tables.

If Table 1 only had 1 record for each invoice (which showed the sum of check amounts applied to the invoice) I would be fine. How can I get around this? Is there, for instance, a way to take Table 1 and from it create a new table that would sum multiple entries and provide a distinct column for invoice number and a column for the total check amounts applied?

There is no other table with the data I need. Here is my CR command.

select
*
from
rm30201
inner join
sop30200
on
rm30201.aptodcnm=sop30200.sopnumbe
left join
sop30300
on
sop30200.sopnumbe=sop30300.sopnumbe

 

[IanWaterman]

In your command use a subquery on RM30201 which only returns one record

I do not know columns but will be something like

select
*
from
select (invoiceNo, aptodcnm, sum((invAmount) from rm30201
group by invoiceNo, aptodcnm) as rm30201
inner join
sop30200
on
rm30201.aptodcnm=sop30200.sopnumbe
left join
sop30300
on
sop30200.sopnumbe=sop30300.sopnumbe

Ian

 

[bradlee27514]

In other words

Say the rm30201 table has the following:

invoice number | check amount
00101 | $100
00101 | $150
00102 | $50

I want it to change to the following:

invoice number | check amount
00101 | $250
00102 | $50

From there I would link to the sales tables on invoice number.

 

[bradlee27514]

Ian, with two select statements?

 

[IanWaterman]

The sub query

select (invoiceNo, aptodcnm, sum((invAmount) from rm30201
group by invoiceNo, aptodcnm)


Will effectively do that for you. This nested query then behaves like a table giving you the summary info you want and thus eliminating the duplicates when you have partial payments (more than 1 check per invoice).

Ian

 

[bradlee27514]

select
(aptodcnm, actualapplytoamount, sum(actualapplytoamount))
from
rm30201
group by
(aptodcnm, actualapplytoamount)
as
rm30201


I get
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

 

[IanWaterman]

YOu have too many brackets and can not group by the summary should be

(select
aptodcnm, sum(actualapplytoamount) as totalapplyamount
from
rm30201
group by
aptodcnm)
as
rm30201

YOu can only alias when as subquery, if you want to test data use

select
aptodcnm, sum(actualapplytoamount) as totalapplyamount
from
rm30201
group by
aptodcnm

Ian

 

[bradlee27514]

(select
aptodcnm, sum(actualapplytoamount) as totalapplyamount
from
rm30201
group by
aptodcnm)
as
rm30201
inner join
sop30200
on
rm30201.aptodcnm=sop30200.sopnumbe
left join
sop30300
on
sop30200.sopnumbe=sop30300.sopnumbe


I get Incorrect syntax near keyword 'as' DB Vendor Code 156. I don't see any incorrect syntax??? Thank you so much for your help!

 

[bradlee27514]

same thing with

select
*
from
(select
aptodcnm, sum(actualapplytoamount) as totalapplyamount
from
rm30201
group by
aptodcnm
as
rm30201
inner join
sop30200
on
rm30201.aptodcnm=sop30200.sopnumbe
left join
sop30300
on
sop30200.sopnumbe=sop30300.sopnumbe

 

[bradlee27514]

sorry,

select
*
from
(select
aptodcnm, sum(actualapplytoamount) as totalapplyamount
from
rm30201
group by
aptodcnm)
as
rm30201
inner join
sop30200
on
rm30201.aptodcnm=sop30200.sopnumbe
left join
sop30300
on
sop30200.sopnumbe=sop30300.sopnumbe

seems to have worked, but looks like i'm still getting duplicates.

 

[IanWaterman]

Use the subquery as a command on its own and look at the recordset in crystal. This may help you determine what is causing the duplicates.

If recodset is unique then the other tables may be causing dupes too.

Ian

 

[bradlee27514]

Please disregard, those appear to be early test invoices. I don't know how to thank you. This is extremely helpful, you are the light.