return results for those matching to either column A or B

[boheili]

Hi,

I have a report (contains sales#, order# and sales rep. name) and a sales list (contains sales#) this month. I am trying to write a query to present the sales rep. names with sales in the month. However, the sales# column in the sales list mixed with order# and sales#:

Sales List:
Column A - 111, bbb

Report:
Column A - 111, 222
Column B - aaa, bbb
Column C - John, Karl

May I ask, how to join the column A in the sales list to both Column A & B in the report, to return column C?


Really appreciate it!!!

[June7]

Why don't you use the actual field names in your posted question?

What do you mean by 'mixed with'? I don't understand the data as represented. Sales List shows 111, bbb in same field but the report shows them in separate fields - how does that happen?

You have a table of sales reps? Is there a field in Sales List table with the reps ID?

[boheili]

Thanks for your reply, June7.

Let me tell you a little bit more about the background.

1. ORDER# is assigned when an order is initialized in the system; a unique SALES# will be assigned to the ORDER# when the product is delivered.

2. The sales list is an excel file maintained by our operation team manually, to track the sales of the month. For the SALES# field in the file, it's supposed to contain only SALES#. However, our operation team sometimes put order# instead... So we will see that the SALES# field is actually a "mix" of ORDER# and SALES#...

3. The report mentioned above is a report exported from the system. It contains SALES#, ORDER# and sales rep. name. They are well separated in fields as they are supposed to be.

4. Here's the elaboration of the situation and my question:


Sales List:
SALES#/ORDER# - 111, bbb
Sales amount - $10, $20

Report:
SALES# - 111, 222, 333
ORDER# - aaa, bbb, ccc
Sales rep. name - John, Karl, Marry

In simple words, I try to write a query to search for ORDER# and sales rep. name based on the SALES#/ORDER# in the sales list. The results should look like this:
SALES#/ORDER# - 111, bbb
ORDER# - aaa, bbb
Sales rep. name - John, Karl
Sales amount - $10, $20

As an Access date base newbie, I really don't know how to express this "conditional match" logic. Really appreciate it if anyone could help!

Why don't you use the actual field names in your posted question?

What do you mean by 'mixed with'? I don't understand the data as represented. Sales List shows 111, bbb in same field but the report shows them in separate fields - how does that happen?

You have a table of sales reps? Is there a field in Sales List table with the reps ID?

[June7]

How would a Sales# be distinguished from Order#?

[boheili]

In real life, the ORDER# is a 13-digit combination; the SALES# is a 7-digit combination. Is it possible to distinguish the #s by matching them with the fields in the report? Like, 1) returning the report.order# by matching saleslist.sales#/order# to report.sales# field, 2) if null, then by matching to report.order# field.

I am comfortable to express this logic in EXCEL, but don't know how to write them in SQL:

=index(report.order#, iferror(match(saleslist.sales#/order#,report.sales#,0),match(saleslist.sales#/order#,report.order#,0)), 1)

[June7]

A query can calculate the Order# and Sales# into separate columns.

SELECT IIf(Len([SALES#/ORDER#])=13, [SALES#/ORDER#], Null) AS OrderNum, IIf(Len([SALES#/ORDER#])=7, [SALES#/ORDER#], Null) AS SalesNum, [Sales Amount] FROM [Sales List];

But I don't think that will get you where you want.

A UNION query can combine the Sales# and Order# fields of the Report table into one field.

SELECT [Sales#] AS Sales_Order_Num, [Sales Rep], [Sales Amount] FROM Report
UNION SELECT [Order#], [Sales Rep], [Sales Amount] FROM Report;

Now build another query that joins the UNION to the Sales List table.



BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[boheili]

Thank you, June7! Got your logic. That's really helpful!