Marking One Record in a Group

[lemo]

hi.
first of all, i am glad to join this lovely forum. i've read some threads, people here are very very helpful..
now the question (please bear with me if it's too basic, i am relatively new to Access):
i've created a 'select' query which does everything for me, except one thing - it doesn't mark one case for a certain group of records. here is the excerpt of how it looks, the last column being the one i can't figure out how to do:

Prop Date Category ID ID_unique
x1 2/3/08 001 2351 1
x1 2/5/08 001 2351 0
x1 2/6/08 001 2351 0
x1 2/9/08 001 2351 0
x1 2/3/08 001 2352 1
x1 2/4/08 001 2352 0
x1 2/3/08 001 2354 1
x1 2/4/08 001 2354 0
x1 2/8/08 001 2354 0

so for each group of records with the same ID, i need to mark only one of them.
is it possible?
thanks.. l

 

[georgedwilkinson]

Yes, but you did not supply your sql or what the criteria is/are for which record gets marked.

Is there a reason you don't have unique identifiers or is that something in the background we don't see? If so, why not just use the unique identifier from the source?

I would do this with a union query.

You also shouldn't use Date as a column name, if that's what you've done, since Date is a reserved word.

 

[lemo]

thanks george.
below is the full sql statement. i just thought it was too complex, so i shrunk it to its essence.

SELECT IIf([Allsites]![Boro]="b","Brooklyn",IIf([Allsites]![Boro]="m","Manhattan",IIf([Allsites]![Boro]="q","Queens",IIf([Allsites]![Boro]="r","S.I.",IIf([Allsites]![Boro]="x","Bronx"))))) AS Borough, ALLSITES.District, ALLSITES.PropNum, ALLSITES.[Prop ID], StrConv([ALLSITES].[Prop Name],3) AS [Prop Name], StrConv([ALLSITES].[Site Name],3) AS [Site Name], ALLSITES.ZipCode, ALLSITES.Acres, InspectionMain.Date, InspectionMain.[Inspection Year], InspectionMain.Season, InspectionMain.Round, IIf([InspectionMain]![Overall Condition]="a",1,IIf([InspectionMain]![Overall Condition]="u",0,IIf([InspectionMain]![Overall Condition]="n","N"))) AS OC, IIf([InspectionMain]![Cleanliness]="a",1,IIf([InspectionMain]![Cleanliness]="u",0,IIf([InspectionMain]![Cleanliness]="n","N"))) AS C, FeatureRatings.Feature, IIf([featureratings]![rating]="a",0,IIf(([featureratings]![rating]="u" Or [featureratings]![rating]="u/s"),1,"N")) AS Rating, ALLSITES.Category, ALLSITES.[Sub-Category], IIf(([Allsites]![district] & [Allsites]![propnum])=([tempLPName_Dist]![district1] & [tempLPName_Dist]![propnum1]),StrConv([tempLPName_Dist]![Prop Name1],3),IIf([Allsites]![category]="Large Park",StrConv([Allsites]![prop name],3),IIf([ALLSITES]![Site Name]=[ALLSITES]![Prop Name],StrConv([ALLSITES]![Prop Name],3) & " (" & [ALLSITES]![Prop ID] & ")",StrConv([ALLSITES]![Site Name],3) & " (" & StrConv([ALLSITES]![Prop Name],3) & ", " & [ALLSITES]![Prop ID] & ")"))) AS [SP, GS Name, ID], Year([Date]) AS [Year], IIf(Month([Date])>=7,Year([Date])+1,Year([Date])) AS [Fisc Yr], [Inspection Year] & "-" & [Season] AS [Yr-Seas], IIf([bodi]="Bronx01" Or [bodi]="Bronx03" Or [bodi]="Bronx04" Or [bodi]="Bronx06" Or [bodi]="Bronx10" Or [bodi]="Bronx11" Or [bodi]="Bronx12" Or [bodi]="Brooklyn01" Or [bodi]="Brooklyn11" Or [bodi]="Brooklyn13" Or [bodi]="Brooklyn18" Or [bodi]="Manhattan10" Or [bodi]="Manhattan12" Or [bodi]="Manhattan12A" Or [bodi]="Queens07" Or [bodi]="Queens08" Or [bodi]="Queens13" Or [bodi]="Queens14" Or [bodi]="S.I.03",[borough] & " ParkStats","") AS [ParkStats Districts], [borough] & [district] AS BoDi, InspectionMain.[Inspection ID], DFirst(1,[inspectionmain]![inspection id],1) AS Expr1
FROM ((ALLSITES INNER JOIN InspectionMain ON ALLSITES.[Prop ID] = InspectionMain.[Prop ID]) INNER JOIN FeatureRatings ON InspectionMain.[Inspection ID] = FeatureRatings.[Inspection ID]) LEFT JOIN tempLPName_Dist ON (ALLSITES.Category = tempLPName_Dist.Category1) AND (ALLSITES.PropNum = tempLPName_Dist.PropNum1) AND (ALLSITES.Boro = tempLPName_Dist.Boro1) AND (ALLSITES.District = tempLPName_Dist.District1)
WHERE (((InspectionMain.Date) Between #7/1/2007# And #2/22/2008#) AND ((InspectionMain.Round)<>0) AND ((ALLSITES.Category)="Greenstreet" Or (ALLSITES.Category)="large park" Or (ALLSITES.Category)="Small Park") AND ((InspectionMain.[Mock Inspection?])=0))
ORDER BY IIf([Allsites]![Boro]="b","Brooklyn",IIf([Allsites]![Boro]="m","Manhattan",IIf([Allsites]![Boro]="q","Queens",IIf([Allsites]![Boro]="r","S.I.",IIf([Allsites]![Boro]="x","Bronx"))))), ALLSITES.District, ALLSITES.PropNum, ALLSITES.[Prop ID], InspectionMain.Date;

 

[georgedwilkinson]

This query seems to be much more than what you wanted information on. It seems that there are many unique things about your data set. So why are you trying to mark one field as unique?

You also haven't said what you think makes a row unique. In your original data set, what is "unique" about a row that would make you want to mark it? What would that original set look like if it were marked in the way you want?

You'll find your sql is easier to read/maintain/troubleshoot/share if you'll do the Borough lookups in a table instead of in an IIF statement. Same with Cleanliness, bodi, featureratings, and any other field that you need to use IIF statements on.

 

[lemo]

- the result of this query will be used by excel pivot table(s), and i want the excel file to have everything it needs in this dataset, hence unique identifiers;

- the row will be unique because it has the same "inspection id", but different "features" (and possibly "ratings"). actually, let me simplify the sql so that it includes the bare minimum to describe my problem. the sql below only produces 3 fields: feature, rating and inspection id -

SELECT FeatureRatings.Feature, IIf([featureratings]![rating]="a",0,IIf(([featureratings]![rating]="u" Or [featureratings]![rating]="u/s"),1,"N")) AS Rating, InspectionMain.[Inspection ID]
FROM ((ALLSITES INNER JOIN InspectionMain ON ALLSITES.[Prop ID] = InspectionMain.[Prop ID]) INNER JOIN FeatureRatings ON InspectionMain.[Inspection ID] = FeatureRatings.[Inspection ID]) LEFT JOIN tempLPName_Dist ON (ALLSITES.Category = tempLPName_Dist.Category1) AND (ALLSITES.PropNum = tempLPName_Dist.PropNum1) AND (ALLSITES.Boro = tempLPName_Dist.Boro1) AND (ALLSITES.District = tempLPName_Dist.District1)
WHERE (((InspectionMain.Date)>=#4/1/2008#));

here is the excerpt of the result -
Feature Rating Inspection ID field i'm dreamin of
Trees 0 56252 1
Athletic Fields 0 56252 0
Trails 0 56252 0
Weeds 0 56254 1
Graffiti 0 56254 0
Trees 1 56254 0

- i know about 'borough' etc lookups, but it would take me much longer back and forth-ing with the dbase dept to have them post the tables i need for this. i do it only when the number of iif clauses gets out of hand..
thanks..

 

[georgedwilkinson]

Why did you pick "Trees" and "Weeds" to be the "unique" row? Is it just the first row that comes up?

 

[lemo]

yep. they just came up as first records in their respective inspection.. btw, there may be more or less than 3 different 'features' per inspection..

 

[georgedwilkinson]

select
min([propnum]) as propnum, [Feature Rating], [Inspection ID], 1 as [field i'm dreaming of]
from (lemosmassivelylongquery)
group by [Feature Rating], [Inspection ID], [field i'm dreaming of]
union
select
[propnum], [Feature Rating], [Inspection ID], 0 as [field i'm dreaming of]
from lemosmassivelylongquery as lmlq1, (select min(lmlq3.propnum), lmlq3.[Feature Rating], lmlq3.[Inspection ID] from lemosmassivelylongquery lmlq3 group by lmlq3.[Feature Rating], lmlq3.[Inspection ID]) as lmlq2
where
lmlq1.[Inspection ID] = lmlq2.[Inspection ID]
and lmlq1.[Feature Rating] <> lmlq2.[Feature Rating]

Concept only here. You'll need to test/troubleshoot it. Make sure there's no possibility of a cartesian product. If you need to order by, enclose it all in a bigger select statement.

 

[lemo]

thanks George.
this is pretty complicated for my level of access knowledge, so it will take me a while to make it work.. i'll let you know.
l

 

[georgedwilkinson]

Just make your original query into a query and use that where I referenced "lemosmassivelylongquery".

Also, what you're doing is complicated.

 

[lemo]

after spending all day yesterday trying to make your concept work, i've pretty much given up. not bright enough, i guess.. if you have a chance George, could you please briefly explain the principle behind it.
on the bright side, i managed to come up with my own way of doing this task. sort of. it involves running a 'make-table' query that creates a table with only one record per [inspection id] group (the min of ([inspection id] & [feature])). then i can link it to other tables, and have it mark only the records when there is a match.. not sure i can use it in practice though.
l

 

[georgedwilkinson]

The principle is:
1. write a query that will produce the "unique" row. Since you didn't define how you would define a unique row, I extrapolated it from one of your queries, guessing that "propnum" was probably the unique thing in your query.
2. write a query that returns all rows except the row in #1 above. The only way I know to do that is to include the first query in a sub-query and say: "don't return those rows".
3. union the two together.

I'm glad you were able to get what you needed!

 

[lemo]

thanks a bunch George.
i am still struggling, but i think i'm almost there.
just need a break...
have a great weekend!
l