Query to remove mult. lines in table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table which includes contract numbers and the account numbers accessing these contracts. If account numbers access more than one contract they will be listed multiple times for each contract they use. I need a table which includes all account info. but list each acct. only one time. I created a query using the summary function FIRST in the account number field. This worked for many accts. but for some it deleted all lines instead of listing just once. Can you advise why this happened? Is there a better way? Thanks
 
Hi,


GROUP on each fields that you want keep (each different values), use
FIRST on all other fields.


Hoping it may help,
Vanderghast, Access MVP

BB Sue said:
I have a table which includes contract numbers and the account numbers
Click to expand...
accessing these contracts. If account numbers access more than one contract
they will be listed multiple times for each contract they use. I need a
table which includes all account info. but list each acct. only one time. I
created a query using the summary function FIRST in the account number
field. This worked for many accts. but for some it deleted all lines
instead of listing just once. Can you advise why this happened? Is there a
better way? Thanks
 
The only info. I want pulled in my query are the acct. #, name, city, and st fields. The main field is the acct. # field which is grouped by first since I don't want the multiple lines. The others I did not specify a function. Why is this dropping some accounts altogether but some work fine? Not sure what to do to correct. Thanks for your help.
 
Hi,


There is something that does not sound fine to my ears when you say that
you "group by first". Either we Group By, either we use an aggregate, like
First, either we impose a condition WHERE, either we use an EXPRESSION out
of the first two possibilities... The SQL statement should look like:


SELECT [Acct. #], First(name), First(city), First(st)
FROM tableNameHere
GROUP BY [Acct. #]



and then, every value in Acct. # sould be available again, but just once.


If that does not work, can you post the SQL statement Access generates for
you (as plain text, under the SQL view)?



Hoping it may help,
Vanderghast, Access MVP



BB Sue said:
The only info. I want pulled in my query are the acct. #, name, city, and
Click to expand...
st fields. The main field is the acct. # field which is grouped by first
since I don't want the multiple lines. The others I did not specify a
function. Why is this dropping some accounts altogether but some work fine?
Not sure what to do to correct. Thanks for your help.
 
Back
Top