Adding description (1 Viewer)

[frankt68]

Hi!

I have two tables. The first one, the tATC table, contains ATC codes and descriptions. As you can see from the attached access file, it has 5 levels of ATC codes.
The second one, the tConsumption table, contains data on consumption of medicines, including the 5th level ATC codes.

I'd like to make a query that will show all records from tConsumption and all levels with descriptions from tATC in the same row, like in the attached excel file example.

How can I do this?

 

[plog]

You would do this via a subquery. That subquery will be based on tConsumption and will be comprised of every field in it along with 4 calculated fields--one each for each portion of the ATC you want to map. As an example, here are the first 2 code breakouts:

ATC_Level1: Mid(ATC, 1,1)
ATC_Level2: Mid(ATC, 2, 2)

You would make 4 of those fields to extract each level you want (the fifth level you already have in ATC. Let's call that sub-query '_sub1'.

Then to get the data you want you build another query using _sub1. Bring in all the fields from it you want to show, then bring in one instance of tATC and link it from ATC to ATC_Level1 and bring in the Description field, renaming it 'ATC1'. Next, you bring in another instance of ATC, link it from ATC to ATC_Level2 and bring in its Description field, renaming it 'ATC2'. Keep brining in instances until all 5 levels are covered. Run it and that's your data.

 

[jdraw]

Similar to plog's approach, 2 separate queries; but different in the level calculation.

ATCL1---ATCL4 are levels 1 thru 4; ATC is level 5

Mid([ATC],1,1) AS ATCL1
Mid([ATC],1,3) AS ATCL2
Mid([ATC],1,4) AS ATCL3
Mid([ATC],1,5) AS ATCL4


First query called ATCLevels

SELECT tConsumption.Record_ID
	,tConsumption.Drug_ID
	,tConsumption.Drug_Name
	,tConsumption.Unit
	,tConsumption.Quantity
	,tConsumption.Ward_ID
	,tConsumption.Ward_Name
	,tConsumption.Total_Value
	,tConsumption.Month_of_consumtpion
	,tConsumption.Year_of_consumtpion
	,tConsumption.Price
	,Mid([ATC], 1, 1) AS ATCL1
	,Mid([ATC], 1, 3) AS ATCL2
	,Mid([ATC], 1, 4) AS ATCL3
	,Mid([ATC], 1, 5) AS ATCL4
	,tConsumption.ATC
FROM tConsumption;

Second query involving tATC and query ACTLevels (as per plog's post)

SELECT ATCLevels.Record_ID
	,ATCLevels.Drug_ID
	,ATCLevels.Drug_Name
	,ATCLevels.Unit
	,ATCLevels.Quantity
	,ATCLevels.Ward_ID
	,ATCLevels.Ward_Name
	,ATCLevels.Total_Value
	,ATCLevels.Month_of_consumtpion
	,ATCLevels.Year_of_consumtpion
	,ATCLevels.Price
	,ATCLevels.ATCL1
	,tATC.Description
	,ATCLevels.ATCL2
	,tATC_1.Description
	,ATCLevels.ATCL3
	,tATC_2.Description
	,ATCLevels.ATCL4
	,tATC_3.Description
	,ATCLevels.ATC
	,tATC_4.Description
FROM (
	(
		(
			(
				ATCLevels INNER JOIN tATC ON ATCLevels.ATCL1 = tATC.ATC
				) INNER JOIN tATC AS tATC_1 ON ATCLevels.ATCL2 = tATC_1.ATC
			) INNER JOIN tATC AS tATC_2 ON ATCLevels.ATCL3 = tATC_2.ATC
		) INNER JOIN tATC AS tATC_3 ON ATCLevels.ATCL4 = tATC_3.ATC
	)
INNER JOIN tATC AS tATC_4 ON ATCLevels.ATC = tATC_4.ATC
ORDER BY ATCLevels.Record_ID;

Sample of the output attached as jpg (left side is cut off to show levels and descriptions on right.

 

[frankt68]

Thank you plog and jdraw for your advice and solution. Exactly what I was looking for.