If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Cross Join - Please Help!
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". |
#3
|
|||
|
|||
Cross Join - Please Help!
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 |
#4
|
|||
|
|||
Cross Join - Please Help!
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 |
#5
|
|||
|
|||
Cross Join - Please Help!
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 |
#6
|
|||
|
|||
Cross Join - Please Help!
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 |
Thread Tools | |
Display Modes | |
|
|