Not Getting All Results

[Rustin788]

Hey,

I am trying to set up a query that will be able to show customers buying history. Currently I have it set up to show all of 2013, 6 months ago, 5months ago... down to the past month. My problem is I can not get it to show me all the records. When I run my final query (which pulls from a bunch of other queries) it gives me 223 results when the # of customers in that time period is actually in the 600 range. I have tried erasing all the table relationships in the query but that just resulted in a query that after 20 minutes still had not finished. I have started a separate query using SQL that allows me to pull all the customers from the other queries that looks like the first quote box below and it shows the customers that should be in the final result. In the final query the formula I have for each column is: Last Month: IIf([Total Cases 1 Month]![Total]=0,0,[Total Revenue 1 Month]![SumOfSumOfMerchandiseValue]/[Total Cases 1 Month]![Total])

What can I do to get the final query to show me all the appropriate results.

SELECT [Customer]
FROM [Total Cases 2013]
UNION SELECT [Customer]
FROM [Total Revenue 2013];
UNION SELECT [Customer]


FROM [Total Cases 6 Months];
UNION SELECT [Customer]
FROM [Total Revenue 6 Months];
UNION SELECT [Customer]
FROM [Total Cases 5 Months];
UNION SELECT [Customer]
FROM [Total Revenue 5 Months];
UNION SELECT [Customer]
FROM [Total Cases 4 Months];
UNION SELECT [Customer]
FROM [Total Revenue 4 Months];
UNION SELECT [Customer]
FROM [Total Cases 3 Months];
UNION SELECT [Customer]
FROM [Total Revenue 3 Months];
UNION SELECT [Customer]
FROM [Total Cases 2 Months];
UNION SELECT [Customer]
FROM [Total Revenue 2 Months];
UNION SELECT [Customer]
FROM [Total Cases 1 Month];
UNION SELECT [Customer]
FROM [Total Revenue 1 Month];

Thanks for any advise you can give.

[orange]

See this material from Martin Green regarding vba and sql and Dynamic Reports.

You do not need all of the queries you have in my view.
Good luck.

[Rustin788]

Thanks, I will take a look at that.