Group By Apt ID - Match like codes

  • Thread starter ironwood9 via AccessMonster.com
  • Start date
I

ironwood9 via AccessMonster.com

My company provides cable service to apartment buildings, with separate codes
for each type of service – we had an error with our close where some of the
amounts were wrong, so we need to compare the old postings with the new
postings.

The service codes and their related amounts for old (original posting) and
new are rarely on the same line if there is a match – is there a way to look
for a match AT THE APARTMENT CODE level, and put them on the same line for
that apartmentID ?

so for each unique AptID, go from this:

AptID…..OldCode...OldAmt...NewCode...NewAmt
...7864....FF..........93.53.......KG..........123.60
...7864....NG.........74.22.......FF...........103.53
...7864.....JH…..…..19.49.......NG............89.22
...7864...(blank)....................FD............94.33
...7864...(blank)....................JH............49.34
...(blank)..(blank)…(blank)..(blank)….(blank)
.. ..8643....JH.........91.54........LM..........125.60
...8643....DE..........73.22.......WR..........129.53
...8643...(blank)...................JH..............99.22
 
M

Marshall Barton

ironwood9 said:
My company provides cable service to apartment buildings, with separate codes
for each type of service – we had an error with our close where some of the
amounts were wrong, so we need to compare the old postings with the new
postings.

The service codes and their related amounts for old (original posting) and
new are rarely on the same line if there is a match – is there a way to look
for a match AT THE APARTMENT CODE level, and put them on the same line for
that apartmentID ?

so for each unique AptID, go from this:

AptID…..OldCode...OldAmt...NewCode...NewAmt
..7864....FF..........93.53.......KG..........123.60
..7864....NG.........74.22.......FF...........103.53
..7864.....JH…..…..19.49.......NG............89.22
..7864...(blank)....................FD............94.33
..7864...(blank)....................JH............49.34
..(blank)..(blank)…(blank)..(blank)….(blank)
. ..8643....JH.........91.54........LM..........125.60
..8643....DE..........73.22.......WR..........129.53
..8643...(blank)...................JH..............99.22
Click to expand...


How close does this kind query get?

SELECT Newtable.AptID,
Newtable.Code As NewCode,
Newtable.Amt As NewAmt,
Oldtable.Code As OldCode,
Oldtable.Amt As OldAmt
FROM Newtable LEFT JOIN OldTable
ON Newtable.AptID = Oldtable.AptID
 
Top