Query to remove mult. lines in table

[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

 

[Michel Walsh]

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

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

 

[Guest]

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.

 

[Michel Walsh]

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

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.