Join Not Displaying Correctly

[spyldbrat]

Is there anyway to show the two distinct records when I created another query that joins the the two together as one line? Or will it always show the two records? I

In the end, I am looking to show the WO# and the Inv # from the Service Channel table on the same line as the PO# from my aging and the invoice that applies. If their is no invoice # in Service Channel for that WO but the invoice exists on the aging for that WO# then I just want the inv# from the Service Channel table to show blank and vice versa.

I have been trying for several days now to somehow remove the second record by using a match or unmatched query and trying to play with different variations of making a query/joins. I have been just failing

UNION query.
UNION query must be typed or copy/paste in SQLView of query builder.

SELECT [Inv #] AS InvNum, [PO #] AS PONum FROM Aging


UNION SELECT [Inv##], [PO#] FROM Service_Channel;

Now you have a dataset of all possible invoice numbers. UNION won't allow duplicate records.

You still have a PO mismatch on invoice 797782. This means you will have two records in the UNION for invoice 797782 because there are 2 PO numbers. This makes 2 distinct records.

Build another query that joins tables to the UNION query.

[June7]

If you allow an invoice to have multiple PO's then yes there will be multiple records for that invoice when joining tables. If you want all the PO's for an invoice to show on one line that requires VBA.

Remove which record? How should query know which to remove? The combination of invoice and PO defines a unique record and both are valid.

[spyldbrat]

"How should the query know which record to remove?" is the question/solution I have been struggling with. Basically, the line that contains the most information wins. if record 1 shows a PO# and but the inv # missing from but record 2 shows the same PO# and an inv# then record 1 should be deleted. I will post an example of what I mean in the morning.

[spyldbrat]

Here is the example: Note: AQInv & AQ are coming from the Join I created above.

Line 2 and line 3 are both showing on Query. Looking at the data, line 2 contains less data the on line 3, therefore, I wouldn't want line 2 to appear on my Query.

AQInv

AQ

FB

Client

Location

Work Order #

PO #

Web Status

Biller

Uploaded Inv #

Amt Entered

Aging Inv #

Inv Amt

Mismatch

Mismatch Var

Portal

PAID

NOT UL

791087

93898761

SRI

Client 1

Loc Name 2

93898761

93898761

INVOICED:CONFIRMED

DRIVERS

791087

5488

791087

$ 5,488.00

Match

Match

Service Channel

99556173

Client 1

Loc Name 1

99556173

99556173

COMPLETED

DRIVERS

801771

$ 3,804.00

Mismatch

Mismatch

Service Channel

Not Uploaded

801771

99556173

Client 1

Loc Name 1

99556173

99556173

COMPLETED

DRIVERS

801771

$ 3,804.00

Mismatch

Mismatch

Service Channel

Not Uploaded

"How should the query know which record to remove?" is the question/solution I have been struggling with. Basically, the line that contains the most information wins. if record 1 shows a PO# and but the inv # missing from but record 2 shows the same PO# and an inv# then record 1 should be deleted. I will post an example of what I mean in the morning.

[June7]

Query needs filter criteria.

Perhaps: WHERE NOT AQInv IS Null

But I suspect the example is not fully representative of the data configurations that will be encountered and that criteria might not satisfy all.

[spyldbrat]

Which column would this go under? I put it under AQ and AQinv and both led the query not yielding any results.

[June7]

Provide the SQL of query that gave that dataset.

[spyldbrat]

Below is the SQL but I just realized that the initial invoice that were missing from my Query that made me start this thread in the first place are still not showing in my Query. The are showing in the Union Query (Aging_Query2) but when I join it with the two other query's they are still not showing. I read I can do a full outer join but I also read that it is not recommended (nor can I figure out how to do this join). I have 3 tables in this Query. I am using fields from Aging_Query 2 to join both tables. I have tried joining these tables various ways but all seem to fall. I am so close, but I know I am missing a piece and just can't figure out where I am going wrong! So now I have two problems -- the not all the invoices from the "Aging_Billers_Query" are appearing in final output and it is displaying duplicate lines.


SELECT DISTINCT [Aging Query2].AQInv, [Aging Query2].AQ, [Aging_ Billers_Query].FB, Service_Channel.Client, Service_Channel.LocationName AS Location, Service_Channel.[W#O##] AS [Work Order #], [Aging_ Billers_Query].[PO #], Service_Channel.Status AS [Web Status], [Aging_ Billers_Query].Biller, Service_Channel.[Inv##] AS [Uploaded Inv #], Service_Channel.[Inv#Amount] AS [Amt Entered], [Aging_ Billers_Query].[Inv #] AS [Aging Inv #], [Aging_ Billers_Query].[Inv Amt], IIf([Inv #]=[Inv##],"Match","Mismatch") AS Mismatch, IIf([Inv#Amount]=[Inv Amt],"Match","Mismatch") AS [Mismatch Var], "Service Channel" AS Portal, IIf([mismatch]="mismatch" And [Aging Inv #] Is Null,"PAID") AS PAID, IIf([inv #]>1 And [Inv##] Is Null,"Not Uploaded") AS [NOT UL]
FROM ([Aging Query2] LEFT JOIN Service_Channel ON [Aging Query2].AQ = Service_Channel.[W#O##]) LEFT JOIN [Aging_ Billers_Query] ON [Aging Query2].AQInv = [Aging_ Billers_Query].[Inv #];