Nested selects

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

Guest

Hello, I need to query a table to find unique values such that given the
below table,

name claimant loss date close date report date name2 amt paid
vermal ashford 010105 020506 020505 vermal $10000
vermal ashford 040105 030406 022805 kinsey $50000
vermal ashford 042505 032805

vermal douglas 010299 021806 vermal
$1000
vermal douglas 010299 040505 021606 lycosa $5000
vermal robbie 030403 040404

I would like the results to be

name claimant loss date close date report date name2 amt paid
vermal ashford 010105 020506 020505 vermal $10000
vermal douglas 010299 021806 vermal
$1000
vermal robbie 030403 040404

I am able to extract with select distinct but I can't extract the respective
columns; i might get any of the loss dates and I only want his row of data.

this is what I have; I get a name and claimant right but the rest may or may
not be their data
SELECT DISTINCT [rolo 4].[clmnt NAME1], First([rolo 4].LOSS_DT) AS
FirstOfLOSS_DT, First([rolo 4].REPORT_DT) AS FirstOfREPORT_DT, First([rolo
4].[close_dt]) as Firstclosedt, Max([rolo 4].STATUS) AS Maxstatus, Max([rolo
4].APPORT) AS MaxOfAPPORT, Max([rolo 4].[Def Status]) AS MaxOfdefstatus,
First([rolo 4].Description) AS firstdes
FROM [rolo 4]
WHERE ((([rolo 4].[Ins LASTNAME])=[Enter Ins Last Name]) AND (([rolo
4].CCS)<>"1"))
GROUP BY [rolo 4].[clmnt NAME1];

Please advise on how best to do this
 
Hi,

What about

SELECT name, claimant, FIRST(lossDate), FIRST(closeDate), FIRST(reportDate),
FIRST(name2), FIRST(amtPaid)
FROM [rolo 4]
GROUP BY name, claimant



Hoping it may help,
Vanderghast, Access MVP
 
Thank you Michel for the quick response. The query i need can't select
arbitrary values for loss date, close date, etc. They must be the values for
the selected individual. I hope I have clarified this.

Michel Walsh said:
Hi,

What about

SELECT name, claimant, FIRST(lossDate), FIRST(closeDate), FIRST(reportDate),
FIRST(name2), FIRST(amtPaid)
FROM [rolo 4]
GROUP BY name, claimant



Hoping it may help,
Vanderghast, Access MVP


Mykas_Robi said:
Hello, I need to query a table to find unique values such that given the
below table,

name claimant loss date close date report date name2 amt
paid
vermal ashford 010105 020506 020505 vermal
$10000
vermal ashford 040105 030406 022805 kinsey
$50000
vermal ashford 042505 032805

vermal douglas 010299 021806 vermal
$1000
vermal douglas 010299 040505 021606 lycosa
$5000
vermal robbie 030403 040404

I would like the results to be

name claimant loss date close date report date name2 amt
paid
vermal ashford 010105 020506 020505 vermal
$10000
vermal douglas 010299 021806 vermal
$1000
vermal robbie 030403 040404

I am able to extract with select distinct but I can't extract the
respective
columns; i might get any of the loss dates and I only want his row of
data.

this is what I have; I get a name and claimant right but the rest may or
may
not be their data
SELECT DISTINCT [rolo 4].[clmnt NAME1], First([rolo 4].LOSS_DT) AS
FirstOfLOSS_DT, First([rolo 4].REPORT_DT) AS FirstOfREPORT_DT, First([rolo
4].[close_dt]) as Firstclosedt, Max([rolo 4].STATUS) AS Maxstatus,
Max([rolo
4].APPORT) AS MaxOfAPPORT, Max([rolo 4].[Def Status]) AS MaxOfdefstatus,
First([rolo 4].Description) AS firstdes
FROM [rolo 4]
WHERE ((([rolo 4].[Ins LASTNAME])=[Enter Ins Last Name]) AND (([rolo
4].CCS)<>"1"))
GROUP BY [rolo 4].[clmnt NAME1];

Please advise on how best to do this
Click to expand...
Click to expand...
 
Hi,


Using FIRST (or LAST) on each field not in the GROUP BY clause will do
exactly that: all the values will come from the SAME record.


Hoping it may help,
Vanderghast, Access MVP

Mykas_Robi said:
Thank you Michel for the quick response. The query i need can't select
arbitrary values for loss date, close date, etc. They must be the values
for
the selected individual. I hope I have clarified this.

Michel Walsh said:
Hi,

What about

SELECT name, claimant, FIRST(lossDate), FIRST(closeDate),
FIRST(reportDate),
FIRST(name2), FIRST(amtPaid)
FROM [rolo 4]
GROUP BY name, claimant



Hoping it may help,
Vanderghast, Access MVP


Mykas_Robi said:
Hello, I need to query a table to find unique values such that given
the
below table,

name claimant loss date close date report date name2 amt
paid
vermal ashford 010105 020506 020505 vermal
$10000
vermal ashford 040105 030406 022805 kinsey
$50000
vermal ashford 042505 032805

vermal douglas 010299 021806 vermal
$1000
vermal douglas 010299 040505 021606 lycosa
$5000
vermal robbie 030403 040404

I would like the results to be

name claimant loss date close date report date name2 amt
paid
vermal ashford 010105 020506 020505 vermal
$10000
vermal douglas 010299 021806 vermal
$1000
vermal robbie 030403 040404

I am able to extract with select distinct but I can't extract the
respective
columns; i might get any of the loss dates and I only want his row of
data.

this is what I have; I get a name and claimant right but the rest may
or
may
not be their data
SELECT DISTINCT [rolo 4].[clmnt NAME1], First([rolo 4].LOSS_DT) AS
FirstOfLOSS_DT, First([rolo 4].REPORT_DT) AS FirstOfREPORT_DT,
First([rolo
4].[close_dt]) as Firstclosedt, Max([rolo 4].STATUS) AS Maxstatus,
Max([rolo
4].APPORT) AS MaxOfAPPORT, Max([rolo 4].[Def Status]) AS
MaxOfdefstatus,
First([rolo 4].Description) AS firstdes
FROM [rolo 4]
WHERE ((([rolo 4].[Ins LASTNAME])=[Enter Ins Last Name]) AND (([rolo
4].CCS)<>"1"))
GROUP BY [rolo 4].[clmnt NAME1];

Please advise on how best to do this
Click to expand...
Click to expand...
Click to expand...
 
Back
Top