Prompt for variable in Query...

[MSherfey]

I think this is what I want to do, but maybe someone can tell me if it is a good idea or how to do it.....please.

I have an Access 2007 table which contains my company's invoice sales since 2000. I have a ton of queries (about 120) which slice/dice the data into usable information (New Customers, Retention Customers, Attrition Customers, etc) for each year as well as their invoice quantity and amount. Right now I have the product I'm searching for 'hard coded' in the query. Since we have quite a few products I want to do the same analysis for all the different products but don't want to create duplicate queries for each one. I also don't want to edit each query to change the product.

Is it possible for Access to prompt me with the product name before it runs the query? Also, is it possible for Access to prompt me for the product as well as show a list for me too choose from? This would be soooo helpful. If so, how?

Thank you in advance for any help you can provide!!

 

[wiklendt]

yup. search either this forum or access help for "parameter query".

from what you describe, all those 120 queries could potentially be slashed to about... 1, maybe 2.

 

[gemma-the-husky]

in the criteria column just put

[enter selected product]

because there is no field called [enter selected product] access will prompt for it. the trouble is, that is often tricky to get exactly the right spelling for the product - or if you use numbered product codes, you wont necessarily know the number for the product you want. Also you can only find one at a time. You might be able to do like [enter product] to get assorted matches - try it and see.

the awkwardness of this is why most developers, instead of using a parameter query, use a selection form, and get the required code from a combo box, or something similar - which is probably what you will look at next

but this should get you started

 

[MSherfey]

Thank you both for your replies. I found out about the [product name] after I did some digging and you're right, getting the name correct is going to be an issue. For now I'm placing it in another table and using that for the name. Then all I have to do is change the name in one table and it will change all the queries. I think the next step for me will be to look into forms.

I'm very curious on what wiklendt said about getting my queries down to a few. That would save me so much time you have no idea! Is it possible to reduce or improve my methodology below:

Currently I run a query for customer retention starting in each year and look for duplicate values in the following years. I also add a few other fields like license type, reseller ID, etc to the results. So for 2000 I have 8 queries: FY00-01 Retention, FY00-02 Retention, FY00-03 Retention, etc. I do this for each year: FY01-02, FY01-03, etc.... I do it this way to get trending information for my reports. I can see the changing numbers across the last 9 years for customers with New Licenses, Renewal Licenses, Maintenance Licenses, etc.

Below is just the code for the FY00-01 Retention query. This is done for each year in 2000 (8 queries), 2001 (7 queries), etc (36 queries all together). Now add this to the attrition queries, accounts which don't exist in the following years (36 queries), and the various other queries I'm running and you can see how this is becoming way too much.

SELECT [FY01 TCC].[EndUser OCN:], [FY01 TCC].[Customer Name:], [FY01 TCC].[Geo1:], [FY01 TCC].[Geo2:], [FY01 TCC].[Customer Size Segment:], [FY01 TCC].[Industry:], [FY01 TCC].[REVENUE TYPE:], [FY01 TCC].[Item Class Dtl:], [FY01 TCC].[DIR INDIR DTL:], [FY01 TCC].[Program Code Rpt:]
FROM [FY00 TCC] INNER JOIN [FY01 TCC] ON [FY00 TCC].[EndUser OCN:] = [FY01 TCC].[EndUser OCN:]
GROUP BY [FY01 TCC].[EndUser OCN:], [FY01 TCC].[Customer Name:], [FY01 TCC].[Geo1:], [FY01 TCC].[Geo2:], [FY01 TCC].[Customer Size Segment:], [FY01 TCC].[Industry:], [FY01 TCC].[REVENUE TYPE:], [FY01 TCC].[Item Class Dtl:], [FY01 TCC].[DIR INDIR DTL:], [FY01 TCC].[Program Code Rpt:]
ORDER BY [FY01 TCC].[EndUser OCN:];

Is it possible to have a query look at IDs for a starting year and compare that against all following years and return them (along with additional fields) in one query? How? Please say yes

 

[MSherfey]

I got a crosstab query to show what I was looking for. This reduced my queries from 70+ to 1. That is a great time saver. Now my only problem is getting it to show unique values. For the life of me I can't get anything other than count in the value section. Any ideas?

How do you get a crosstab query to show unique values in the value section?

Arggghhhhhhh!

 

[wiklendt]

crosstabs are not for showing the field values - they are for counting, summing, averaging etc.... (you can either use the wizard or change the type of mathematical action in the dropdown of the query design just under the field name there somewhere) usually most useful in accounting/finance/invoices/sales etc. where you are wanting totals in each year etc.

i have an ordering database which i created for our lab at work (LOL, as opposed to my lab at home, right?). i have one form, where i can choose beginning/end/duration dates and/or an employee. then i press a command button that i have programmed to invoke a report to base on that query (the dates/names i select on the form provide automatically the parameter - so it's a parameter query which doesn't prompt the use b/c the user already has provided that parameter when they chose the dates/names in the dropdowns on the form...) and it shows me all the figures/details for the dates/people i selected.

there's more to it than just that (i actually needed some clever VBA to make it work properly) but work it does

sounds like that might be the way for you to begin with your 'retention' queries (sorry, you lost me in all the jargon - didn't really undestand what data you were extracting from your tables)...

which leads me to ask - do you have normalised tables? i suspect you do if you could reduce 70+ queries to just 1 (told you it could be done! ) but just have to ask on a matter of principle i'm sure you understand

 

[MSherfey]

I'm not sure if I understand 'normalized' correctly. My data is pretty raw. I have all the sales (Invoice) data for one of our product lines from 2000-2008. The table has quite a few fields like Customer ID, Size, Quantity, $ Amount, etc.; about 15 fields in total. That's it, very straight forward.

From there I chop it into smaller bits with my queries:
1) Customer data for each year, about 5 fields based on Fiscal Year. (8 queries) The reason this isn't done in a cross tab is I can't have more than one value field. I like to show customer ID, quantity, and amount per Geo, size, new vs renewal, etc. The crosstab won't allow this.
2) New Customer data (customers which did not exist in the prior year). This is based on the above query. Basically customers in year x which do not exist in year x-1. This makes about 8 queries.
3) Customers who buy only once. This is based on query 1 as well. Customers who buy in year x, but do not buy in year x+1, x+2, x+3, etc.
4) Customers who continue to buy. Again, based on query 1. Customers who buy in year x and also buy in x+1, x+2, etc.

As you can see, each one of these makes about 8 or so queries and that starts to add up quickly. Then the fun really starts. Now I have to put these in Excel so I can start to trend them. This requires a ton of export/imports and data editing. Way too manual.

The crosstab seems to be the answer if I can only get the customer counts to be unique. I can run one cross tab for each type of data (Total customers, New, staying, leaving, etc) and have it already trended based on year. But since I can only have one data value and it doesn't do unique counts, it doesn't help too much.

I don't know if I've answered your 'normalization' question or not. I'll read up on the topic and add more.

Thank you for all your feedback so far. Your ideas have helped point me in a new direction which is helping quite a bit! Thanks!