Problem with query.

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

ielmrani via AccessMonster.com

Hi everyone:

I have the following data in a query:

ID Cust NAMECUS SumOfBASE Assests Lives

615 Cohn Real 5000
692 Gora 18 19387
5
692 Gora 833.06 19387
5
692 Gora 850.03 19387
5
692 Gora 833.06 19387
5
692 Gora 843.11 19387
5
692 Gora 1750 19387
5
585 Terwin 100 2841
2
585 Terwin 300 2841
2
585 Terwin 2415 2841
2
585 Terwin 500 2841
2
652 Waterview 325

I would like to show Assests and Lives only once for each customer, so my
final query will be like this:

ID Cust NAMECUS SumOfBASE Assests Lives

615 Cohn Real 5000
692 Gora 18 19387
5
692 Gora 833.06

692 Gora 850.03
692 Gora 833.06
692 Gora 843.11
692 Gora 1750
585 Terwin 100 2841
2
585 Terwin 300
585 Terwin 2415
585 Terwin 500
652 Waterview 325

I hope this is clear. Thanks in advance

Ismail
 
Sorry the data is not in order. Here is the query again.

ID Cust NAMECUS SumOfBASE Assests Lives

615 Cohn Real 5000
692 Gora 18 19387 5
692 Gora 833.06 19387 5
692 Gora 850.03 19387 5
692 Gora 833.06 19387 5
692 Gora 843.11 19387 5
692 Gora 1750 19387 5
585 Terwin 100 2841 2
585 Terwin 300 2841 2
585 Terwin 2415 2841 2
585 Terwin 500 2841 2
652 Waterview 325

Result:

ID Cust NAMECUS SumOfBASE Assests Lives

615 Cohn Real 5000
692 Gora 18 19387 5
692 Gora 833.06
692 Gora 850.03
692 Gora 833.06
692 Gora 843.11
692 Gora 1750
585 Terwin 100 2841 2
585 Terwin 300
585 Terwin 2415
585 Terwin 500
652 Waterview 325

Thanks
 
You cannot do that in a query. Well, I guess you could if you had some
unique id for the returned row and wanted to write some fairly complex code
to decide whether or not to show the value in the row or to show null. I
don't see anything in your posted data which would allow me to decide which
record is the one that should display the data.

If you are doing this in a report, the solution may be fairly simple. You
can use the hide duplicates properties of the relevant controls to hide the
duplicate values in Assests and Lives.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thank you for your help. I was able to fix the problem using report.

John said:
You cannot do that in a query. Well, I guess you could if you had some
unique id for the returned row and wanted to write some fairly complex code
to decide whether or not to show the value in the row or to show null. I
don't see anything in your posted data which would allow me to decide which
record is the one that should display the data.

If you are doing this in a report, the solution may be fairly simple. You
can use the hide duplicates properties of the relevant controls to hide the
duplicate values in Assests and Lives.
Sorry the data is not in order. Here is the query again.
Click to expand...
[quoted text clipped - 86 lines]
Click to expand...
 
Back
Top