Cross Join - Please Help!

Hi,

I have two tables - one has all of my company sales data (MCB Sales Data)
with many columns and the other (Rep State Product Assignment) has the states
and products that each reps are assigned to. The "Rep State Product
Assignment" Table has 6 columns, 5 of which are exactly the same as columns
in the "MCB Sales Data" table. The 6th column just says "Correctly Booked"
for all rows. The purpose of having the second table is that oftentimes, a
sale will be input into the system incorrectly (meaning that a sale will be
attributed to a rep for a product that doesn't belong to him/her or in a
state that he/she isn't assigned to.

What I would like to do is write a query that looks through the five columns
in the MCB Sales Data Table that are the same as those in the Rep State
Product Assignment Table and whenever the combination of the 5 fields in one
row has a match in the Rep State Product Assignment Table, write "Correctly
Booked in a column of its own (for that row). Otherwise, if those 5 criteria
don't have a match in the MCB Sales Data Table then write "Misbooked" in the
new column.

I tried to do this by left joining the two tables on the five criteria and
among other things, asking the query to return the 6th field in the Rep State
Product Assignment Table. The problem was that my query table, which
originally had 340,852 rows (the same number of rows as the MCB Sales Data
Table) now had 473,892 rows.

Any ideas for how I can achieve what I'm trying to accomplish or why my rows
got increased by so much?

Thanks,
Michael

On Jul 14, 9:17*am, MichaelR
wrote:
Hi,

I have two tables - one has all of my company sales data (MCB Sales Data)
with many columns and the other (Rep State Product Assignment) has the states
and products that each reps are assigned to. The "Rep State Product
Assignment" Table has 6 columns, 5 of which are exactly the same as columns
in the "MCB Sales Data" table. The 6th column just says "Correctly Booked"
for all rows. The purpose of having the second table is that oftentimes, a
sale will be input into the system incorrectly (meaning that a sale will be
attributed to a rep for a product that doesn't belong to him/her or in a
state that he/she isn't assigned to.

What I would like to do is write a query that looks through the five columns
in the MCB Sales Data Table that are the same as those in the Rep State
Product Assignment Table and whenever the combination of the 5 fields in one
row has a match in the Rep State Product Assignment Table, write "Correctly
Booked in a column of its own (for that row). Otherwise, if those 5 criteria
don't have a match in the MCB Sales Data Table then write "Misbooked" in the
new column.

I tried to do this by left joining the two tables on the five criteria and
among other things, asking the query to return the 6th field in the Rep State
Product Assignment Table. The problem was that my query table, which
originally had 340,852 rows (the same number of rows as the MCB Sales Data
Table) now had 473,892 rows.

Any ideas for how I can achieve what I'm trying to accomplish or why my rows
got increased by so much?

Thanks,
Michael

Perhaps there is a misunderstanding of "left join".

The outer join criteria means all rows in the left ( first mentioned )
table should appear, without regard to whether a corresponding row
exists in the right ( second mentioned ) table.

The query must be written as such

SELECT [MCB Sales Data].*, iif( IsNull( [Rep State Product
Table].BookingStatus ), 'Misbooked', [Rep State
Product].BookingStatus ) as [Correctly Booked?]
FROM [MCB Sales Data] left join [Rep State Product] on
( [MCB Sales Data].Criteria1 = [Rep State Product].Criteria1 )
and
( [MCB Sales Data].Criteria2 = [Rep State Product].Criteria2 )
and
( [MCB Sales Data].Criteria3 = [Rep State Product].Criteria3 )
and
( [MCB Sales Data].Criteria4 = [Rep State Product].Criteria4 )
and
( [MCB Sales Data].Criteria5 = [Rep State Product].Criteria5 )


All rows in [MCB Sales Data] will be returned. If all five criteria
are not matched, the word "Misbooked" will appear in the sixth
column. Otherwise, "Correctly booked" will appear.

Evidently, your query is generating a two rows for each incorrectly
booked entry, one that reads "Correctly Booked" and one that reads
"Misbooked".

This is how the FROM clause of my query looked like before I added the Rep
State Prod Assignment table:

FROM ((([MCB Sales Data] LEFT JOIN [Rep Names] ON [MCB Sales Data].CSACTSP =
[Rep Names].[Rep Num]) LEFT JOIN BookMonth ON [MCB Sales Data].CSFPR =
BookMonth.MonthNumber) LEFT JOIN [Company Table] ON ([MCB Sales Data].CSDIV =
[Company Table].CSDIV2) AND ([MCB Sales Data].CSCO = [Company Table].CSCO2))
LEFT JOIN ActivityMonth ON [MCB Sales Data].CSAFPR =
ActivityMonth.ActMonthNumber;

This query returned the correct number of rows (340,852). Before even
putting the IIF statement that you recommended into the query, I just added
your suggestion for joining the tables so that the resulting FROM clause
looked like this:

FROM (((([MCB Sales Data] LEFT JOIN [Rep Names] ON [MCB Sales Data].CSACTSP
= [Rep Names].[Rep Num]) LEFT JOIN BookMonth ON [MCB Sales Data].CSFPR =
BookMonth.MonthNumber) LEFT JOIN [Company Table] ON ([MCB Sales Data].CSDIV =
[Company Table].CSDIV2) AND ([MCB Sales Data].CSCO = [Company Table].CSCO2))
LEFT JOIN ActivityMonth ON [MCB Sales Data].CSAFPR =
ActivityMonth.ActMonthNumber) LEFT JOIN [Rep State Prod Assignments] ON ([MCB
Sales Data].CSACTSP = [Rep State Prod Assignments].CSACTSP3) AND ([MCB Sales
Data].CSSTATE = [Rep State Prod Assignments].CSSTATE3) AND ([MCB Sales
Data].CSPROD = [Rep State Prod Assignments].CSPROD3) AND ([MCB Sales
Data].CSDIV = [Rep State Prod Assignments].CSDIV3) AND ([MCB Sales Data].CSCO
= [Rep State Prod Assignments].CSCO3);

The last few lines contain the part that I added on your recommendation.
When I went to the datasheet view, my query table had 473,892 rows again. Do
you have any idea why this might be happening? Did I do something wrong?

Thanks,
Michael

I would guess that there are duplicates in the two tables base on the five
fields you are using to make the join. If you simplify the query to just the
two tables you would have a FROM clause that looked something like the following.


SELECT S.*
, IIF(S.CSACTSP3 is Null,"Uh-oh something's wrong","Correctly Booked")
FROM [MCB Sales Data] as S LEFT JOIN [Rep State Prod Assignments] As A
ON [S].CSACTSP = [A].CSACTSP3
AND [S].CSSTATE = [A].CSSTATE3
AND [S].CSPROD = [A].CSPROD3
AND [S].CSDIV = [A].CSDIV3
AND (S].CSCO = [A].CSCO3

If this works, you can add back all your lookup tables and join them to MCB
Sales Data. You may be able to "combine" the duplicates by changing the query to

SELECT DISTINCT S.*
, IIF(S.CSACTSP3 is Null,"Uh-oh something's wrong","Correctly Booked")
FROM [MCB Sales Data] as S LEFT JOIN [Rep State Prod Assignments] As A
ON [S].CSACTSP = [A].CSACTSP3
AND [S].CSSTATE = [A].CSSTATE3
AND [S].CSPROD = [A].CSPROD3
AND [S].CSDIV = [A].CSDIV3
AND (S].CSCO = [A].CSCO3

If that does not eliminate the duplicates then your query will get more complex.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

MichaelR wrote:
This is how the FROM clause of my query looked like before I added the Rep
State Prod Assignment table:

FROM ((([MCB Sales Data] LEFT JOIN [Rep Names] ON [MCB Sales Data].CSACTSP =
[Rep Names].[Rep Num]) LEFT JOIN BookMonth ON [MCB Sales Data].CSFPR =
BookMonth.MonthNumber) LEFT JOIN [Company Table] ON ([MCB Sales Data].CSDIV =
[Company Table].CSDIV2) AND ([MCB Sales Data].CSCO = [Company Table].CSCO2))
LEFT JOIN ActivityMonth ON [MCB Sales Data].CSAFPR =
ActivityMonth.ActMonthNumber;

This query returned the correct number of rows (340,852). Before even
putting the IIF statement that you recommended into the query, I just added
your suggestion for joining the tables so that the resulting FROM clause
looked like this:

FROM (((([MCB Sales Data] LEFT JOIN [Rep Names] ON [MCB Sales Data].CSACTSP
= [Rep Names].[Rep Num]) LEFT JOIN BookMonth ON [MCB Sales Data].CSFPR =
BookMonth.MonthNumber) LEFT JOIN [Company Table] ON ([MCB Sales Data].CSDIV =
[Company Table].CSDIV2) AND ([MCB Sales Data].CSCO = [Company Table].CSCO2))
LEFT JOIN ActivityMonth ON [MCB Sales Data].CSAFPR =
ActivityMonth.ActMonthNumber) LEFT JOIN [Rep State Prod Assignments] ON ([MCB
Sales Data].CSACTSP = [Rep State Prod Assignments].CSACTSP3) AND ([MCB Sales
Data].CSSTATE = [Rep State Prod Assignments].CSSTATE3) AND ([MCB Sales
Data].CSPROD = [Rep State Prod Assignments].CSPROD3) AND ([MCB Sales
Data].CSDIV = [Rep State Prod Assignments].CSDIV3) AND ([MCB Sales Data].CSCO
= [Rep State Prod Assignments].CSCO3);

The last few lines contain the part that I added on your recommendation.
When I went to the datasheet view, my query table had 473,892 rows again. Do
you have any idea why this might be happening? Did I do something wrong?

Thanks,
Michael

John,

The first query returned 473,892 rows again but the second one that you
wrote returned the correct number. Will the "combination" of duplicates have
any effect on my sales numbers or will they be the same as the original sales
data table?

How can I apply the distinct function to my original query that does other
things as well? I don't need the whole table (S*) - only some of the fields.
Could I put something like select distinct CSACTSP?

Although it's a little bit long, my query without the Rep State Prod
Assignment Table looks like this:

SELECT [Company Table].Company, [MCB Sales Data].CSACTSP AS [Rep Number],
IIf([rep num]=405 And [CSCO]=7 Or [CSCO]=8,"HARRY SCHWARTZ",[Rep Name]) AS
[Sales Rep Name], [MCB Sales Data].CSSLD AS Account, BookMonth.MonthLetters
AS [Book Month], ActivityMonth.ActMonthLetter AS [Ship/Can/Ret Month],
IIf([CSPROD]="RM","RM ",[CSPROD]) AS Product, [MCB Sales Data].CSSTATE AS
State, IIf([CSCTYP]="B","Bridal",IIf([CSCTYP]="S","Social",[CSCTYP])) AS
Type, [MCB Sales Data].[CSBKD$] AS [Net Booked $], IIf([CSBKD$]0,0,[CSBKD$])
AS [Gross Booked $], [MCB Sales Data].CSBKDU AS [Net Booked Units],
IIf([CSBKDU]0,0,[CSBKDU]) AS [Gross Booked Units], [CSSHP$]-[CSRET$] AS
[Shipped $], [MCB Sales Data].CSSHPU AS [Shipped Units], [MCB Sales
Data].[CSRET$] AS [Returned $], [MCB Sales Data].CSRETU AS [Returned Units],
[MCB Sales Data].[CSCAN$] AS [Cancelled $], [MCB Sales Data].CSCANU AS
[Cancelled Units], IIf([CSCO]=7 Or
[CSCO]=8,"Canada",IIf([CSState]="-","International","US")) AS Geography,
IIf([CSDEPT]="BUDGET" And [CSFYR]=2008,"2008B",[CSFYR]) AS [Book Year], [MCB
Sales Data].CSAFYR AS [Ship/Can/Ret YEAR]
FROM [Company Table] RIGHT JOIN (ActivityMonth RIGHT JOIN (BookMonth RIGHT
JOIN ([MCB Sales Data] LEFT JOIN [Rep Names] ON [MCB Sales Data].CSACTSP =
[Rep Names].[Rep Num]) ON BookMonth.MonthNumber = [MCB Sales Data].CSFPR) ON
ActivityMonth.ActMonthNumber = [MCB Sales Data].CSAFPR) ON ([Company
Table].CSCO2 = [MCB Sales Data].CSCO) AND ([Company Table].CSDIV2 = [MCB
Sales Data].CSDIV);

Thank you for your help so far!

Michael

The asterisk is short hand for list all fields. So, yes, you can list just
the fields you want to see. Include the tablename and fieldnames in the
format [Table Name].[Field Name]

If the field name is unique over the tables in the FROM clause, you can just
list the field name.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

MichaelR wrote:
John,

The first query returned 473,892 rows again but the second one that you
wrote returned the correct number. Will the "combination" of duplicates have
any effect on my sales numbers or will they be the same as the original sales
data table?

How can I apply the distinct function to my original query that does other
things as well? I don't need the whole table (S*) - only some of the fields.
Could I put something like select distinct CSACTSP?

Although it's a little bit long, my query without the Rep State Prod
Assignment Table looks like this:

SELECT [Company Table].Company, [MCB Sales Data].CSACTSP AS [Rep Number],
IIf([rep num]=405 And [CSCO]=7 Or [CSCO]=8,"HARRY SCHWARTZ",[Rep Name]) AS
[Sales Rep Name], [MCB Sales Data].CSSLD AS Account, BookMonth.MonthLetters
AS [Book Month], ActivityMonth.ActMonthLetter AS [Ship/Can/Ret Month],
IIf([CSPROD]="RM","RM ",[CSPROD]) AS Product, [MCB Sales Data].CSSTATE AS
State, IIf([CSCTYP]="B","Bridal",IIf([CSCTYP]="S","Social",[CSCTYP])) AS
Type, [MCB Sales Data].[CSBKD$] AS [Net Booked $], IIf([CSBKD$]0,0,[CSBKD$])
AS [Gross Booked $], [MCB Sales Data].CSBKDU AS [Net Booked Units],
IIf([CSBKDU]0,0,[CSBKDU]) AS [Gross Booked Units], [CSSHP$]-[CSRET$] AS
[Shipped $], [MCB Sales Data].CSSHPU AS [Shipped Units], [MCB Sales
Data].[CSRET$] AS [Returned $], [MCB Sales Data].CSRETU AS [Returned Units],
[MCB Sales Data].[CSCAN$] AS [Cancelled $], [MCB Sales Data].CSCANU AS
[Cancelled Units], IIf([CSCO]=7 Or
[CSCO]=8,"Canada",IIf([CSState]="-","International","US")) AS Geography,
IIf([CSDEPT]="BUDGET" And [CSFYR]=2008,"2008B",[CSFYR]) AS [Book Year], [MCB
Sales Data].CSAFYR AS [Ship/Can/Ret YEAR]
FROM [Company Table] RIGHT JOIN (ActivityMonth RIGHT JOIN (BookMonth RIGHT
JOIN ([MCB Sales Data] LEFT JOIN [Rep Names] ON [MCB Sales Data].CSACTSP =
[Rep Names].[Rep Num]) ON BookMonth.MonthNumber = [MCB Sales Data].CSFPR) ON
ActivityMonth.ActMonthNumber = [MCB Sales Data].CSAFPR) ON ([Company
Table].CSCO2 = [MCB Sales Data].CSCO) AND ([Company Table].CSDIV2 = [MCB
Sales Data].CSDIV);

Thank you for your help so far!

Michael