One to many (duplicates)

[texasalynn]

I have a query with two tables that ends up with duplicates. I'm not sure how to explain, but one table will have multiple types of service for each account, but the other table only has one line for each account. How can I get the data to display all the data but only leave the duplicate lines with blanks on those fields? My tables are too big to attach, but hopefully someone will understand and be able to help.

Thanks . . . texasalynn

 

[Philem]

Hullo. Try using the Group By option in your query. That should help.

HTH

P

 

[texasalynn]

No because that doubles the amounts

texasalynn

 

[SydneyGeek]

Try Phil's idea -- but in the data fields (the ones where you don't want duplicated values) pick First instead of Group for the option.

HTH

Denis

 

[texasalynn]

Thanks for the suggestions but it still lists the duplicates. Basically I want from this data:

svc23 acct1
svc31 acct1
svc37 acct2
svc55 acct2
svc72 acct2
svc25 acct3
svc51 acct3

to display like this:

svc23 acct1
svc31
svc37 acct2
svc55
svc72
svc25 acct3
svc51

HTH
texasalynn

 

[bat17]

Where is this data being shown? if it is for a report I think that you could hide it in the report itself.

Peter

 

[SydneyGeek]

Note sure you can do that in a query (easily, at least)... but I agree with bat

If you created a report with grouping on the second, acct1 etc. field, that would give you what you need. The layout would be:
acct1
srvc21
srvc23
acct2
srvc35
srvc36

and so on.

HTH

Denis

 

[texasalynn]

I agree that a report is the best place to "DISPLAY" data the way I want but the problem is that I have to export the results to Excel and it's been my experience that reports don't always export all the data correctly. If someone has a suggestion for that or even how to get it into Excel the way I want that would be a bonus!!!!

HTH
texasalynn

 

[SydneyGeek]

Aha! If you want to get it into Excel, you can do the cleanup there -- that's much easier than trying to do it in Access.

1. Run the query, giving you all the data you want (plus the extra info).
2. Transfer the data into Excel
3. In Excel, run a macro with code like this:

Sub CleanUnwantedAcctCodes()
'Assuming that the Acct codes are in Column B
Range("B65536").End(xlUp).Select
Do Until ActiveCell.Row=2
If ActiveCell.Value=ActiveCell.Offset(-1,0).Value Then
ActiveCell.ClearContents
End If
ActiveCell.Offset(-1,0).Select
Loop
End Sub

HTH

Denis