Multiple query combination - unique values

[PeterZ7]

Hi all. I'm a newbie to access and have a question I hope someone might be able to shed light on and point me in the right direction.

Have 5 separate queries that pull transaction data based on different variables. I need to combine the results of all 5 queries into one dataset, from which I'll produce a report to distribute 2x per day (so I can't manually do it). Transactions IDs returned within each query are unique, but may appear in one or many of the 5 queries. My final output must be Transaction ID, and the originating query (reason) or reasons it is on the list. Could be just one, or all five. The number of transaction IDs in each query will vary by day, as each query is independent of all others.

Simple Example (using 3 queries):
Qry1:
ID# 001 Reason 1
ID# 003 Reason 1
ID# 005 Reason 1

Qry 2:
ID# 001 Reason 2
ID# 012 Reason 2

Qry3:
ID# 003 Reason 3

What I need:

ID# Reason(s)
------- --------------------------
001 Reason 1 and 2
003 Reason 1 and 3
005 Reason 1
012 Reason 2

Can anyone help me with this? I appreciate any and all feedback. Thanks for looking, and thanks in advance for your help.
-Peter

 

[plog]

I would suggest doing this all in one query. Most likely with a custom function that returns the Reason value.

Can you zip your database and upload it?

 

[MarkK]

Why must those results be returned by separate queries? What I would expect to see is a result like this generated by a single query...

QryAll:
ID# 001 Reason 1
ID# 003 Reason 1
ID# 005 Reason 1
ID# 001 Reason 2
ID# 012 Reason 2
ID# 003 Reason 3

...and then a single report, grouped by ID, and having a details section showing the reasons...

001 
   Reason 1 
   Reason 2
003 
   Reason 1
   Reason 3
005 
   Reason 1
012 
   Reason 2

That would be the simplest approach, structurally, to the problem you describe. So what I would want to look at first is why you need 5 queries. What is the exact query SQL for a couple of the queries? Can we look at why we can't return this all in one shot?

 

[PeterZ7]

My limited knowledge of Access / SQL

All Queries performed in Access,
Below are SQL views

Query 1:
SELECT ClaimData.[Rx #], ClaimData.[Is COB], ClaimData.[Generic Indicator], ClaimData.Code, ClaimData.Adjudicated, ClaimData.Sold, ClaimData.[Dispensed Name], ClaimData.[Dispensed NDC (with format)], ClaimData.[TP Code], ClaimData.Name, ClaimData.[TP Total Amt], ClaimData.[Patient Pay Amount], ClaimData.[Total Claim Price], ClaimData.[Acquisition Cost], [Total Claim Price]-[Acquisition Cost] AS [Gross Profit], ([Gross Profit]/[Total Claim Price])*100 AS [Margin %]
FROM ClaimData
WHERE (((ClaimData.Adjudicated)>[Forms]![DateForReports]![Dates]) AND ((ClaimData.[TP Code])>="70000" And (ClaimData.[TP Code])<="70012") AND ((ClaimData.[TP Total Amt])>50))
ORDER BY ClaimData.[TP Total Amt] DESC;
Query 2:
SELECT RxData.[Rx #], RxData.[Is COB], RxData.[Generic Indicator], "" AS Code, RxData.Adjudicated, RxData.[Sold Date], RxData.[Product Name], RxData.[Product NDC (with format)], RxData.Code, RxData.Name, RxData.[TP Total Amt], RxData.[Patient Pay Amount], "" AS [Total Claim Price], RxData.[Acquisition Cost], "" AS [Gross Profit], RxData.[Margin %], "" AS Blank1, "CoPay Card" AS Blank2, "" AS Blank3, "" AS Blank4, "" AS Blank5
FROM CopayExclusions RIGHT JOIN (RxData INNER JOIN CoPayRef ON RxData.[Product NDC (with format)] = CoPayRef.NDC) ON CopayExclusions.ins = RxData.Name
GROUP BY RxData.[Rx #], RxData.[Is COB], RxData.[Generic Indicator], RxData.Adjudicated, RxData.[Sold Date], RxData.[Product Name], RxData.[Product NDC (with format)], RxData.Code, RxData.Name, RxData.[TP Total Amt], RxData.[Patient Pay Amount], RxData.[Acquisition Cost], RxData.[Margin %], "CoPay Card", "", CopayExclusions.ins, "", "", "", "", "", ""
HAVING (((RxData.Adjudicated)>[Forms]![DateForReports]![Dates]) AND ((CopayExclusions.ins) Is Null));
Query 3:
SELECT ClaimData.[Rx #], ClaimData.[Is COB], ClaimData.[Generic Indicator], ClaimData.Code, ClaimData.Adjudicated, ClaimData.Sold, ClaimData.[Dispensed Name], ClaimData.[Dispensed NDC (with format)], ClaimData.[TP Code], ClaimData.Name, ClaimData.[TP Total Amt], ClaimData.[Patient Pay Amount], ClaimData.[Total Claim Price], ClaimData.[Acquisition Cost], [Total Claim Price]-[Acquisition Cost] AS [Gross Profit], ([Gross Profit]/[Total Claim Price])*100 AS [Margin %]
FROM ClaimData
WHERE (((ClaimData.Code)<>"2") AND ((ClaimData.Adjudicated)>[Forms]![DateForReports]![Dates]) AND ((ClaimData.[Fill #])="0"))
ORDER BY ClaimData.[TP Total Amt] DESC;
Query 4:
SELECT ClaimData.[Rx #], ClaimData.[Fill #], ClaimData.Code, ClaimData.Adjudicated, ClaimData.Sold, ClaimData.[Product Name], ClaimData.[Dispensed Name], ClaimData.DrLast, ClaimData.[Patient First], ClaimData.[Patient Last], ClaimData.Name
FROM ClaimData
WHERE (((ClaimData.Adjudicated)>[Forms]![DateForReports]![Dates]) AND ((ClaimData.[Generic Indicator])="B" Or (ClaimData.[Generic Indicator])="M") AND ((ClaimData.[TP Code])>="70000" And (ClaimData.[TP Code])<="70009") AND ((ClaimData.[Is Primary])="Y"));

Query 5:
SELECT ClaimData.[Rx #], ClaimData.[Is COB], ClaimData.[Generic Indicator], ClaimData.Code, ClaimData.Adjudicated, ClaimData.Sold, ClaimData.[Dispensed Name], ClaimData.[Dispensed NDC (with format)], ClaimData.[TP Code], ClaimData.Name, ClaimData.[TP Total Amt], ClaimData.[Patient Pay Amount], ClaimData.[Total Claim Price], ClaimData.[Acquisition Cost], [Total Claim Price]-[Acquisition Cost] AS [Gross Profit], ([Gross Profit]/[Total Claim Price])*100 AS [Margin %]
FROM ClaimData
WHERE (((ClaimData.Adjudicated)>[Forms]![DateForReports]![Dates]) AND ((ClaimData.[TP Code])<>"70001" And (ClaimData.[TP Code])<>"70002" And (ClaimData.[TP Code])<>"70003" And (ClaimData.[TP Code])<>"70004" And (ClaimData.[TP Code])<>"70005" And (ClaimData.[TP Code])<>"70007") AND ((ClaimData.[TP Total Amt])>0) AND (([Total Claim Price]-[Acquisition Cost])<0))
ORDER BY ClaimData.[TP Total Amt] DESC;

 

[PeterZ7]

Thanks for your willingness to help. I attempted to make this report consolidation effort as simple as possible because we're purchasing an add on product that will make this effort obsolete in 90 days.

Essentially, I'm thinking I can modify the queries to return just the Rx# for each query and do a make table, leaving me with 5 independent tables.

As it stands now, record counts for each table are as follows:
Tbl A: 500 records
Tbl B: 130 records
Tbl C: 110 records
Tbl D: 75 records
Tbl E: 69 records

Comparing A to others gives the following duplicate record counts:
A-B -17
A-C 33
A-D 44
A-E 34

That's 128 duplicates, which reduces the entire record count from 884 to 756.
Further relationships show:
B-C 2 dupes
B-D 42 dupes
B-E 0 dupes

So, now I'm down to 712 unique Rx records

C-D 9 dupes
C-E 10 dupes

Which leavess 693 records in my final report.

I assume (which is not always good), that this is a SQL Join Issue that I've yet to encounter and will leave me with the number of records I logically (or illogically) concluded fit what I'm looking for.

Sorry if I confused you with my ignorance.

Peter

 

[MarkK]

Not all these queries return the same fields. If you want to combine them into one dataset, what fields will be in that dataset?

It seems awkward that you have two tables, one named ClaimData and one name RxData, that seem to contain the same fields. This is a complication. A more common structure is that all those records be in the same table, and that there be a boolean field called IsRX, which would equal True for RxData records.

It can't be overstated how much easier it is to filter out records from one big table than it is to try and knit together records from different tables. The first is fast and easy, the second is slow and hard.

 

[PeterZ7]

The tables are direct extracts from an off the shelf pharmacy dispensing application my company uses. The data in the tables represent different moments in the lifespan of a prescription and values for similar fields vary between the two, but are still important in their own respect.
Combining the tables is a good suggestion and something I will look into in the future.

With my limited skills, I can create a table with two fields; Rx# and a text field with a term denoting the query that landed them in the table. Then, I'd be left with a table of duplicate Rxs (where applicable) with unique terms in field two. A possible solution is to then, for each unique Rx# create a third field that puts the one, two, three, four or five terms associated with each Rx# in a string, removing the duplicative Rx#. From there, I can append the appropriate fields required to be on the printed report.

Any ideas on this? Thanks so much for your help. I truly appreciate it.
-peter

 

[MarkK]

What I would do is to try and take the expressions out of the WHERE clauses and express them as field values instead. See if it makes sense what is going on here...

SELECT ClaimData.[Rx #],
[TP Code] IN ['70000', '70012'] And [TP Total Amt] > 50 As Reason1,
Code <> '2' AND [Fill #] = '0' As Reason3
FROM ClaimData
WHERE Adjudicated > [Forms]![DateForReports]![Dates]

See how Reason1 and Reason3 become calculated boolean field values in the query, rather than WHERE clause constraints? This may give you some ideas about how to simplify your process.

But it's a complication if you need to look in multiple tables.

hth

 

[PeterZ7]

Appreciate the help and speedy responses. Trying to keep this as simple as possible, and I don't mind running several queries to get this done. Most important, is to get this information on paper as soon as possible using a process which is repeatable and accurate. Each of my 5 queries produce results which define the "reason" I mentioned. If append a term to the rightmost field of the make table query, the last piece of the puzzle is to solve this issue below. I've simplified it for


ColumnA ColumnB
1 abc
1 pqr
1 xyz
2 efg
2 hij
3 asd
My question is, how can I concatenate the values in the second column to a row value based on the first column. The query results that I want is as follows:

ColumnA ColumnB
1 abc, pqr, xyz
2 efg, hij
3 asd

Any ideas?
Thanks!

 

[plog]

To answer your question, try this link:

http://allenbrowne.com/func-concat.html

However, I really think for a best practice point of view you need to simply it from 5 queries to one. But the link above will do you what you want.

 

[PeterZ7]

Cool. I will check this out. Here's where I am now:

I created a make table query from my Qry1 and 4 append queries (my queries 2-5) which creates a table with two fields:
Rx # Blank1
Where Blank 1 is the designated term denoting which query contributed to the Rx being added to the final result. If I can just concatenate all the terms for each Rx #, thereby reducing redundant Rx #s I will be left with a working dataset from which I can create a report. I found an example and tweaked it to work with my database. Unfortunately, it is limited to 2 items concatenated. There are instances in my data that there could be 3 or even 4. My effort now is to modify the query I borrowed to allow for more than two items. The SQL is as follows:
Merge340 is my table which contains two fields; Rx # and Blank1 (reason term)
This works and provides a unique list of Rx #s with either one or two reason terms separated by a comma. My challenge is to modify this SQL to allow for 3 or even 4.
Any ideas how I can get this done?
Thanks again. Appreciate your efforts greatly!

SELECT Merge340.[Rx #] AS [Rx #], First([Merge340].[Blank1]) & IIf(Count([Merge340].[Blank1])>1,"," & Last([Merge340].[Blank1])) AS Blank1
FROM Merge340
GROUP BY Merge340.[Rx #];