Need query for data extraction (1 Viewer)

syodb

Registered User.
Local time
Yesterday, 18:07
Joined
Sep 7, 2017
Messages
27
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.

Code:
						(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:


Code:
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

AWF VIP
Local time
Yesterday, 21:07
Joined
Jun 21, 2011
Messages
5,901
Hmm, perhaps a *Totals* query using a combination of Max and Last? Have you tried that?
 

JHB

Have been here a while
Local time
Today, 02:07
Joined
Jun 17, 2012
Messages
7,732
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".
 

Attachments

  • Sampledatabase (2).zip
    73.6 KB · Views: 87

syodb

Registered User.
Local time
Yesterday, 18:07
Joined
Sep 7, 2017
Messages
27
June7 , two date1x, datex1 and datex1 can be different dates. date3x>date2x>date1x.
I will try your suggestions.:confused:
 

syodb

Registered User.
Local time
Yesterday, 18:07
Joined
Sep 7, 2017
Messages
27
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.
 
Last edited:

JHB

Have been here a while
Local time
Today, 02:07
Joined
Jun 17, 2012
Messages
7,732
Sorry - you lost me here. :confused:
 

syodb

Registered User.
Local time
Yesterday, 18:07
Joined
Sep 7, 2017
Messages
27
I meant like if the input con_tbl table instead is (see the col1)::


Code:
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

Have been here a while
Local time
Today, 02:07
Joined
Jun 17, 2012
Messages
7,732
See the attachment, run query "qry3Part".
 

Attachments

  • Sampledatabase (2)1.accdb
    596 KB · Views: 87

syodb

Registered User.
Local time
Yesterday, 18:07
Joined
Sep 7, 2017
Messages
27
Hi JHB,
The Sampledatabase (2)1.accdb produces unexpected output. I will work on it, I begin from your second upload.
 

JHB

Have been here a while
Local time
Today, 02:07
Joined
Jun 17, 2012
Messages
7,732
The output by me is:
 

Attachments

  • qry3Part.jpg
    qry3Part.jpg
    30 KB · Views: 182

syodb

Registered User.
Local time
Yesterday, 18:07
Joined
Sep 7, 2017
Messages
27
it is wrong. this is right.

Code:
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

Have been here a while
Local time
Today, 02:07
Joined
Jun 17, 2012
Messages
7,732
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.
 

Attachments

  • Sampledatabase (2)1.accdb
    608 KB · Views: 89

Users who are viewing this thread

Top Bottom