I am having a problem when data is not consistent betweentwo years to get both years to show up.
I have a database which I query by two principle fields in onetable.
1. One field is store number – examples – store 238 and store 101
a. Another field is date – mm/dd/yyyy
2. Another field for Month
3. Another field for Year
Conditions:
1. Store 238 has sales in 2011, but closed in 2012therefore no sales data in table for 2012.
2. Store 101 has sales every Wednesday in bothyears 2011 and 2012.
a. Example: 2011- 8/4/2011 is a Wednesday but in2012 8/6/2012 is a Wednesday.
Problem:
1. My query crosstab has column as store number andvalue as Sales for 2012.
2. I have another field for 2011 sales in the samequery by rows
Store 238 and 101 do not show up in theresults.
Store 238 does not show up because it isnot in 2012
Store 101 does not show up because dates don’tmatch in 2011 and 2012.
I can make two different query crosstabsone for 2012 and one for 2011 and everything shows up fine
But when I combine into a simple query thetwo query crosstabs
Store 238 and 101 do not show up in theresults.
It doesn’t matter which type of join I use. Neither show up.
The join always rules both stores out in the results due today, week, month or full date or store numbers not consistent in both years.
My date field in the query could include full date(mm/dd/yyyy) or Month (mm) or Year (yyyy).
One situation I got both to show up but I needed to add afields for week which excluded both stores again.
I feel like I am 98% there in finding the solution, but justcan’t get it to work.
I really need to figure this out because these specific two problems keep coming up over again when I do a new query to do a new report.
HELP!!!
.