Need query for data extraction

[syodb]

Someone left a table that some how is complicated for me to query the data I want to resemble the output. struggled with the transform then the cross tab, not much luck, could not do it. I need a query that from the input con_tbl table i get the output table.

input con_tbl table::

all fields are of type text.

						(general 
						format)
ID	MyId	Col1	Col2	mydate		Col3
----------------------------------------------------------
1	aaa1	1	xa	datex1		aa
2	aaa1	2	xb	datex2		aa
3	aaa1	2	xc	datex3		bb
4	bbb1	1	xc	datex1		cc
5	ccc1	1	xd	datex1		dd
5	ccc1	1	xe	datex2		ee
6	ddd1	2	xf	datex1		ff
7	eee1	1	xg	datex1		gg
8	eee1	2	xh	datex1		hh
.
.

assuming datex3 > datex2 > datex1, for a given MyId select the row if its col1 = '2' with highest date within the same Col1 setting otherwise if Col1='2' does not exist, then, for that MyId select row with col1 = '1' instead still with highest date within the same COl1 setting. Aim is to catch the rows with col1 = '2' and if not exist catch the row with col1='1'. One row per each distinct MyId.

The output will look like:

MyId  			Col1		Col2  		mydate		Col3
---------------------------------------------------------------------------------------
aaa1     		2     		xc 		datex3		bb
bbb1     		1      		xc   		datex1		cc
ccc1     		1      		xe   		datex2		ee
ddd1     		2      		xf  		datex1		ff
eee1     		2      		xh		datex1		hh
.
.

 

[GinaWhipp]

Hmm, perhaps a *Totals* query using a combination of Max and Last? Have you tried that?

 

[June7]

You want the TOP 1 date record for each MyID group? MyID eee1 shows same date for both records - is this a correct presentation?

Review http://allenbrowne.com/subquery-01.html#TopN

 

[JHB]

The most transparent way is to use 3 queries.
1st query finds the maximum value in Col1, 2nd query finds the maximum value in the MyDate link up against the first query.
The 3rd query finds the values for the remaining fields, link up against the second query.
Sample database attached, run query "qry3Part".

 

[syodb]

June7 , two date1x, datex1 and datex1 can be different dates. date3x>date2x>date1x.
I will try your suggestions.

 

[syodb]

JHB and all, how about if the input con_tbl table, in the col1, substitute 'TB' for every '2' and 'TA' for every '1', applying the same rules?. This is another table that differs a bit but make the query more difficult. Like when 'TB' and 'TA' exist for a given MyId, select the one with 'TB'. I tried to use the iif in my where statement, it did not work. like: where col1 = iif(col1,con_tbl , ....).
your help was nice, thank you and all others.

 

[JHB]

Sorry - you lost me here.

 

[syodb]

I meant like if the input con_tbl table instead is (see the col1)::

ID	MyId	Col1	Col2	mydate		Col3
----------------------------------------------------------
1	aaa1	TA	xa	datex1		aa
2	aaa1	TB	xb	datex2		aa
3	aaa1	TB	xc	datex3		bb
4	bbb1	TA	xc	datex1		cc
5	ccc1	TA	xd	datex1		dd
5	ccc1	TA	xe	datex2		ee
6	ddd1	TB	xf	datex1		ff
7	eee1	TA	xg	datex1		gg
8	eee1	TB	xh	datex1		hh
.
.

 

[JHB]

See the attachment, run query "qry3Part".

 

[syodb]

Hi JHB,
The Sampledatabase (2)1.accdb produces unexpected output. I will work on it, I begin from your second upload.

 

[JHB]

The output by me is:

 

[syodb]

it is wrong. this is right.

ID	MyId	Col1	Col2	mydate		Col3
----------------------------------------------------------
3	aaa1	TB	xc	datex3		bb
4	bbb1	TA	xc	datex1		cc
5	ccc1	TA	xe	datex2		ee
6	ddd1	TB	xf	datex1		ff
8	eee1	TB	xh	datex1		hh
.
.

 

[JHB]

Run query "qry4Part".
Do you understand the principle of how it works?
Otherwise, try to run the queries one by one and see what results they are retrieving.