none
Query that needs to have only 1 row per id RRS feed

  • Question

  • Hello,

    I am having trouble in MS Access 2016 query that I cannot get only one row per idPrimary. Here is what the data looks like:

    shareSerial	idPrimary	serial	category	postingDate	effectiveDate	effectiveBalance	collectionNoticeTypeSerial	collectionNoticeTypeDescription	collectionNoticeCount	collectionNoticeFACTActOption	shareNegativeSinceDays	shareNegativeSinceDate	shareCourtesyPayEnabled	reason	transactionSerial	monetarySerial	effectiveBalanceResult	availableBalance	availableBalanceResult	feePostAmount	feeDescription		
    11584	IDKSOV22NLJGV1GNHHC12MMOF1JIOEU5VGZPYBI2DIPF3ZNSWZM2TC	5086	Courtesy pay	2016-04-27	2016-04-27	-943.05									24849504	24849517	-993.55	-943.05	-993.55				
    5127	IDST50RDD5LL0CGQ0KHMWFASPECDUFFPIV0EZUM3PSMMIKVLKD15AP	5078	Courtesy pay	2016-04-27	2016-04-27	-512.44									24847202	24847206	-580.89	-512.44	-580.89				
    16201	IDWLAAK5XWE0FZNERMGNFGGQWGTFXWBWD4VZ1KDYAAUOSF1TNCLTL	5098	Courtesy pay	2016-04-27	2016-04-27	-27.78									24852347	24852353	-958.94	-27.78	-958.94				
    12189	IDWZBT2HFEJPXCEDV2WCT1MNOEYDONRUWPCY1RI4CWSEVTEQ5QQ04J	5089	Courtesy pay	2016-04-27	2016-04-27	305.66									24850074	24850275	-187.22	305.66	-187.22				
    12189	IDWZBT2HFEJPXCEDV2WCT1MNOEYDONRUWPCY1RI4CWSEVTEQ5QQ04J	5091	Courtesy pay	2016-04-27	2016-04-27	-187.22									24850436	24850443	-487.22	-187.22	-487.22				
    2432	IDKS52YUJA3IJFLJDZUX5VRKPWXFEP3HM3SJWYI5JXCMRKBKZ1CYCB	5103	Courtesy pay	2016-04-27	2016-04-27	-231.98									24853619	24853663	-256.97	-231.98	-256.97				
    3217	IDFOXBW4YHBJW3KJIGLYKAP1REPJ5IAU0CXDRLS4MPZDNZ5NDUUSCN	5130	Courtesy pay	2016-04-27	2016-04-27	-90.13									24861897	24861899	-785.71	-90.13	-785.71				
    30944	IDDYS5G15NIFUKEFC1HM3LAYLC1LFG1ZYOUTHNL5MOA5ZU2RKONZ5N	5124	Courtesy pay	2016-04-27	2016-04-27	-324.04									24860321	24860327	-606.33	-324.04	-606.33				
    33941	ID0GF4LJ1PQ40QLPEG3PIGLWMQ1O5JLK3OYF2D1BGS5XSWXXR2YO4H	5131	Courtesy pay	2016-04-27	2016-04-27	-224.33									24861939	24861946	-274.33	-224.33	-274.33				
    40932	IDSM4O1ALXROEEM0DFBB5GDDSWCJCSN1DBUQTZXYMDGGA1AGP12QVF	5158	Courtesy pay	2016-04-27	2016-04-27	222.17									24866790	24867042	-130.45	222.17	-130.45				
    38570	IDRTQ13CNHHVVYJGOASSLFVVUZFGZ4Z0YVOLZRKLDDEOYKNIVOQR5	5151	Courtesy pay	2016-04-27	2016-04-27	-643.13									24865100	24865380	-19.49	-643.13	-19.49				
    38570	IDRTQ13CNHHVVYJGOASSLFVVUZFGZ4Z0YVOLZRKLDDEOYKNIVOQR5	5154	Courtesy pay	2016-04-27	2016-04-27	-19.49									24865617	24865621	-119.49	-19.49	-119.49				
    38570	IDRTQ13CNHHVVYJGOASSLFVVUZFGZ4Z0YVOLZRKLDDEOYKNIVOQR5	5156	Courtesy pay	2016-04-27	2016-04-27	-119.49									24865742	24865750	-144.49	-119.49	-144.49				
    44755	IDFYIHW1JYU1CIPNOPGOMTZCHBFX303KN4U0KWKJICGQZL24FFXAE	5172	Courtesy pay	2016-04-27	2016-04-27	2.46									24869173	24869433	-12.51	2.46	-12.51				
    14152	ID3BT5DBSEP1X4HQ3BLVCPAIVBZK5QR33TIQGLP4FH5ZYPYDOBCCJI	5102	Courtesy pay	2016-04-27	2016-04-27	0.46									24853115	24853147	-11.45	0.46	-11.45				
    15860	IDQ2UW3WPTR4BOMO1COAIPSQMNJNJ0QHBWHNQ5ZMMP3EPDTSTYK0XG	5099	Courtesy pay	2016-04-27	2016-04-27	-389.78									24852734	24852742	-419.84	-389.78	-419.84				
    35786	ID324SDRWWTCP2KG5B22113FMYHEV0PM2CSDH3AG4MYMRMJYAH01D	5139	Courtesy pay	2016-04-27	2016-04-27										24863478	24863500	-598.57		-598.57				
    35786	ID324SDRWWTCP2KG5B22113FMYHEV0PM2CSDH3AG4MYMRMJYAH01D	5141	Courtesy pay	2016-04-27	2016-04-27	-598.57									24863652	24863655	-616.10	-598.57	-616.10				
    35786	ID324SDRWWTCP2KG5B22113FMYHEV0PM2CSDH3AG4MYMRMJYAH01D	5142	Courtesy pay	2016-04-27	2016-04-27	-616.10									24863788	24863790	-654.10	-616.10	-654.10				
    11645	IDUSY4DJ3E4FXDL43AUKLKECFZGHVOYI3IDTMC5GH4D0EOHBZTV4YE	5150	Courtesy pay	2016-04-27	2016-04-27	-406.70									24864949	24864953	-491.67	-406.70	-491.67				
    36660	IDOQ4B4EO4RFTIBHS1CO2PR4EQ1R4SJSUTTQ3ZFBJ0I2AYDLLPVLN	5140	Courtesy pay	2016-04-27	2016-04-27	464.42									24863534	24863541	-535.58	464.42	-535.58				
    52874	IDZPQY0BHDMJZTHY01BN405TMX0IKJIGTTTY01CJMGCUM05HZDJINK	5185	Courtesy pay	2016-04-27	2016-04-27	26.35									24872387	24872659	-18.75	26.35	-18.75				
    33639	IDVSWOKRUV4G1POJTZZA51D4KT5II2J1D05CDRVGPNAOAL4SXDC1CO	5129	Courtesy pay	2016-04-27	2016-04-27	-851.75									24861804	24861820	-910.72	-851.75	-910.72				
    37069	IDT3DP2U0QSL3GIZHBOR2AJQYTWHEBPWTDIION2HIKAPEL4CAQQRTE	5143	Courtesy pay	2016-04-27	2016-04-27	-381.11									24863882	24863885	-877.37	-381.11	-877.37				
    38553	IDGWKDB5T1YS1EOWHNYQKKNAPZZKGWFZBYEOHLM3IZVBYWF4A32WSN	5149	Courtesy pay	2016-04-27	2016-04-27	-225.65									24864904	24864915	-275.65	-225.65	-275.65				
    

    The problem is the idPrimary and shareSerial have duplicates and the serial is unique. I want to sent out one notice per idPrimary. Does anyone have any ideas? The column serial doesn't need to be in teh query either.


    Thursday, April 28, 2016 4:50 PM

Answers

  • You know what I figured it out. Acccess was forcing me to create another query because it would just crash when I generated the query I was writing. The first query took the first(availableBalance) and left joined the account table something like this.

    SELECT notice.idPrimary, notice.shareSerial, first(availableBalanceResult) AS nAvailableBalanceResult
    FROM notice RIGHT JOIN (SELECT MAX(n.idPrimary) AS maxSerial, first(shareSerial) AS idPrime FROM notice AS n GROUP BY n.idPrimary)  AS n2 ON (notice.shareSerial = n2.idPrime) AND (notice.idPrimary = n2.maxSerial)
    WHERE notice.category = 'Courtesy pay'
    GROUP BY notice.idPrimary, notice.shareSerial;

    Second table just Left joined account table and there we go. I usually use SQL Server so access is a little different. Thank you Ken for looking at my question.

    • Marked as answer by achurchill Thursday, April 28, 2016 5:55 PM
    Thursday, April 28, 2016 5:55 PM

All replies

  • Which columns do you wish to return, and on what basis do you wish to determine which of the rows is to be returned from each subset of rows with the same idPrimary value?

    Ken Sheridan, Stafford, England

    Thursday, April 28, 2016 5:11 PM
  • I want to return category, availableBalanceResult, and there is an accountNumber but that is not in this table. The accountNumber with not be unique. I just want one row per idPrimary it doesn't matter what row teh first second or third.
    Thursday, April 28, 2016 5:30 PM
  • You know what I figured it out. Acccess was forcing me to create another query because it would just crash when I generated the query I was writing. The first query took the first(availableBalance) and left joined the account table something like this.

    SELECT notice.idPrimary, notice.shareSerial, first(availableBalanceResult) AS nAvailableBalanceResult
    FROM notice RIGHT JOIN (SELECT MAX(n.idPrimary) AS maxSerial, first(shareSerial) AS idPrime FROM notice AS n GROUP BY n.idPrimary)  AS n2 ON (notice.shareSerial = n2.idPrime) AND (notice.idPrimary = n2.maxSerial)
    WHERE notice.category = 'Courtesy pay'
    GROUP BY notice.idPrimary, notice.shareSerial;

    Second table just Left joined account table and there we go. I usually use SQL Server so access is a little different. Thank you Ken for looking at my question.

    • Marked as answer by achurchill Thursday, April 28, 2016 5:55 PM
    Thursday, April 28, 2016 5:55 PM