"In any calendar year" as criteria (1 Viewer)

shadow9449 · Nov 25, 2008

I'm trying to write a query for a report that will find all client who have spent more than a certain amount of money in any calendar year during the stated time interval.

The user has a screen where he/she can enter a start date, an end date and a minimum amount. The minimum amount spent would indicate the minimum that the client spent in total in any given year, not during the interval of the query.

Example 1:

User selects between Jan 1, 2005 and Dec 31, 2007, and $100 as the minimum. If a client spent $100 in 2005, this client is included even though he didn't make any purchases in 2006 or 2007

Example 2:
User selects betwee Jan 1, 2006 and Dec 31, 2008 and the same $100 minimum. If a client purchased $90 in goods every year, she will be excluded because she's never made the minimum yearly purchases.

Right now I can't see how this can be done other than using some coding with looping but I'm hoping someone can figure a way to do it using queries.

Many thanks in advance

SHADOW

namliam · Nov 25, 2008

Try a group by query on year... then a subselect or self join...

Aircode to give you the idea...
Select customerID, year(yourdate), sum(amount)
from yourtable
group by customerid, year(yourdate)
having sum(amount) > 100

This will select all the customerIDs that are to be selected.
Now save this query and join it to your customer table to extract the customers selected, done....

Good Luck... ! ! !

DCrake · Nov 25, 2008

Do you want to show the clients spends for the years he didn't spend more than $100 at the same time?

2006 - 150
2007 - 90
2008 - 0

If you only want to show the years they met the condition then you need to group by the year of the sale date and sum the amount spent and add >=100 in the criteria section.

shadow9449 · Nov 25, 2008

namliam said:
Try a group by query on year... then a subselect or self join...

Aircode to give you the idea...
Select customerID, year(yourdate), sum(amount)
from yourtable
group by customerid, year(yourdate)
having sum(amount) > 100

This will select all the customerIDs that are to be selected.
Now save this query and join it to your customer table to extract the customers selected, done....!

That makes a lot of sense. I'll try it and report back.

Thank you!

SHADOW

shadow9449 · Nov 25, 2008

DCrake said:
Do you want to show the clients spends for the years he didn't spend more than $100 at the same time?

2006 - 150
2007 - 90
2008 - 0

If you only want to show the years they met the condition then you need to group by the year of the sale date and sum the amount spent and add >=100 in the criteria section.

I want to show the amount spent for all clients who meet my criteria. The example you gave above would be the output of the query. I'm going to try the solution in the previous post and create a query that consists of all client IDs that meet the criteria. Then run a second, more general query with a join on the client ID field to exclude anyone who doesn't qualify.

Thank you

SHADOW

shadow9449 · Nov 25, 2008

Mailman:

That solution worked perfectly. Thank you so much!

The next part of what I want to do is a bit trickier and may exceed the limitations of Access. I say this after looking through many posts here and on the KB articles and other websites.

I want to write a report showing how much each client spent in every year, but I want to use a crosstab format. This is the format that's most easy to read if you are comparing year to year.

Example:

Code:

                2005     2006     2007
Charlie         $100      $20       $0
Kathy           $200      $90      $75

My problem is that before the user enters the date criteria, I don't know what the column headings will be, nor do I know how many years will be included in the report. The crosstab query itself generates the columns dynamically but the report cannot.

One other thing to point out is that crosstab queries do not do well with criteria parameters or joins. I will have to create a query prior to the crosstab that does the date and minumum-spent criteria and dumps it into a table that will feed the crosstab.

SHADOW

namliam · Nov 25, 2008

shadow9449 said:
Mailman:

That solution worked perfectly. Thank you so much!

Happy to help...

shadow9449 said:
The next part of what I want to do is a bit trickier and may exceed the limitations of Access. I say this after looking through many posts here and on the KB articles and other websites.

No such thing... you can run the world with Microsoft Access!
Some things just take a little more work than others, *insert Joda like voice* but run the world you can

shadow9449 said:
One other thing to point out is that crosstab queries do not do well with criteria parameters or joins. I will have to create a query prior to the crosstab that does the date and minumum-spent criteria and dumps it into a table that will feed the crosstab.

Crosstab queries are just queries... they can work perfectly well with joins and criteria, but if you want to / need to split it into seperate queries... then do so... Step by step is ussualy easiiest.

shadow9449 said:
My problem is that before the user enters the date criteria, I don't know what the column headings will be, nor do I know how many years will be included in the report. The crosstab query itself generates the columns dynamically but the report cannot.

This problem is a bit more real... I have had simular problems/challanges. It certainly wasnt easy figuring this out for the first time and it requires some coding... but doable.

The trick is to use some coding to read the crosstab and manipulate the report a little, wont be fast be will work. THE limitation would be the number of years you can show, i.e. max 5 or 10 or 3 whatever you choose... it will allways be that number of years, or in the case of the crosstab the number of columns, because that is what is the problem with the report.

So if you have say 10 columns/years and the user only requests 5, you create 5 dummy columns to fill the gap.

In normal English, it would work kindof like so:
Query1
Your join/group by count/sum query.
Insert the Parameters thru code.

Query2
Your crosstab query, addapt this with the IN part of the SQL.
This IN part will force columns to be created even if the value is not there (the dummy columns).
I.e. User requests data for 2002 - 2005 but you desided on 5 columns.
So you would (in code again) add to the IN part 2002, 2003, 2004, 2005 and 2006, 2006 beeing dummy.

Query3
This will be the basis for your report, simple
Select * from query2
you need this because Crosstabs are limited in sorting posibilities and such things. You could base the report of the crosstab, but this will cause more problems.

Now in the report you can either "on open" reset your fields to look at the years in the crosstab or...
Manipulate query3 to do something like:
Select [2002] as Year1, [2003] as Year2 etc
to make constant columns for the report to report on. Offcourse in this case you need to manipulate the query everytime the report is run, much like you have to manipulate the Crosstab everytime it is run.

I hope from this you can get the idea and get started on the queries and some code. If you have any problems or my explanation was not clear enough feel free to ask back here.

shadow9449 · Nov 25, 2008

namliam said:
Crosstab queries are just queries... they can work perfectly well with joins and criteria, but if you want to / need to split it into seperate queries... then do so... Step by step is ussualy easiiest.

I think I read that you cannot use a join in a crosstab query. As well, a crosstab query that's going to take parameters needs to have the columns declared and the parameters declared. I can't understand WHY most queries work fine without these requirements whereas Crosstabs give you these headaches. I also have trouble doing the declarations as you get one error after another.

Pre-building a query to feed the crosstab will not solve the problem. Even if there's no criteria in the crosstab, the crosstab will complain that a PREVIOUS query has criteria! The only working solution I've been able to use is to dump the data into a temporary table. In my searching the forum I see that others have done the same thing.

namliam said:
Query1
Your join/group by count/sum query.
Insert the Parameters thru code.

So far no problem but what do you mean by "insert the parameters through code"?

namliam said:
Query2
Your crosstab query, addapt this with the IN part of the SQL.
This IN part will force columns to be created even if the value is not there (the dummy columns).
I.e. User requests data for 2002 - 2005 but you desided on 5 columns.
So you would (in code again) add to the IN part 2002, 2003, 2004, 2005 and 2006, 2006 beeing dummy.

You lost me here.

- Why am I using code to create the query rather than building it and storing it?

- The user typed in a range of dates. I'm going to use something like "Between DATE1 and DATE2" in my initial query of step 1 to make sure to only have the pertinent data. Why am I dealing with 2006 altogether? What if there are no sales in 2006?

namliam said:
Query3
This will be the basis for your report, simple
Select * from query2
you need this because Crosstabs are limited in sorting posibilities and such things. You could base the report of the crosstab, but this will cause more problems.

No problem here.

namliam said:
Now in the report you can either "on open" reset your fields to look at the years in the crosstab

HOW do I get it to pick the years out of the column names of the underlying query??

Thanks again

SHADOW

namliam · Nov 26, 2008

shadow9449 said:
I think I read that you cannot use a join in a crosstab query. As well, a crosstab query that's going to take parameters needs to have the columns declared and the parameters declared.

A crosstab needs to be limited to show only that which you want to see... It is and should be possible to filter data someplace, otherwize you have to cross all your data all the time?? No I am sure it can be done!

shadow9449 said:
So far no problem but what do you mean by "insert the parameters through code"?

Well in code you can read the period the user wants and stick that into the query.
i.e.
UserYear = 2002
Currentdb.Querydefs("YourQuery").SQL = "Select * from yourtable where year(somedate) > " & UserYear

This will put the SQL
Select * from yourtable where year(somedate) > 2002
Into YourQuery, if your crosstab then runs of that it should only pickup that data. Offcourse this is a simple sample, but you can make it as complex as you want.

shadow9449 said:
Why am I dealing with 2006 altogether? What if there are no sales in 2006?

Because the issue, or the bigger issue, is that adding/removing columns in design mode of a report using code is extreemly hard... though possible (I think - I have never actually done it - )
While if you fix the number of columns you have in your report (i.e. 5) then you dont need to redesign the report or anything... Just need to mess around with SQL a bit, maybe change some labels in the report....
But all that (to me) is pretty easy.

shadow9449 said:
No problem here.

Great...

shadow9449 said:
HOW do I get it to pick the years out of the column names of the underlying query??

Well you can do it two ways.
1)
Change the report columns to look at the different years that you need them to look at

2) - the easier option to me -
Dynamicaly (again from code) "standardize" the query/report.
Like I said... I code make query3 be something like
Select [2002] as Year1, [2003] as Year2 etc

You can then run your report using the Year1 and Year2 columns. You allready know the timeframe for you needed to assign the parameters in Query1, thus you know it is 2002 thru 2005 (and add 2006 as dummy) and you can make the colums be what you need them to be in your report.

shadow9449 · Nov 27, 2008

namliam said:
A crosstab needs to be limited to show only that which you want to see... It is and should be possible to filter data someplace, otherwize you have to cross all your data all the time?? No I am sure it can be done!

It can be done but it's not easy:

http://allenbrowne.com/ser-67.html#Param

Please see this part:
But, parameters do not work with crosstab queries, unless you:
a) Declare the parameter, or
b) Specify the column headings.

I tried Allen's technique and it doesn't always work. Actually, you have to do BOTH, not just one, and in my case where the heading names are unknown, this is not possible.

namliam said:
Well in code you can read the period the user wants and stick that into the query.
i.e.
UserYear = 2002
Currentdb.Querydefs("YourQuery").SQL = "Select * from yourtable where year(somedate) > " & UserYear

This will put the SQL
Select * from yourtable where year(somedate) > 2002
Into YourQuery, if your crosstab then runs of that it should only pickup that data. Offcourse this is a simple sample, but you can make it as complex as you want.

There are simpler ways to use criteria in a query, such as giving the user an input box and then referencing that box in the criteria of the query. Either way, same result.

namliam said:
Because the issue, or the bigger issue, is that adding/removing columns in design mode of a report using code is extreemly hard... though possible (I think - I have never actually done it - )
While if you fix the number of columns you have in your report (i.e. 5) then you dont need to redesign the report or anything... Just need to mess around with SQL a bit, maybe change some labels in the report....
But all that (to me) is pretty easy.

I have to figure out a technique for this still. I'm thinking more of creating the colums and leaving them unbound until runtime. If I do that, the extra "columns" can be left unbound and will be blank.

namliam said:
Well you can do it two ways.
1)
Change the report columns to look at the different years that you need them to look at

Right, but how do you get it to derive the column names? There may be a function to read them into an array or something but I just don't know what that function would be.

namliam said:
2) - the easier option to me -
Dynamicaly (again from code) "standardize" the query/report.
Like I said... I code make query3 be something like
Select [2002] as Year1, [2003] as Year2 etc

Yes, that is much easier, just a lot less impressive to the user who wants to know what years his data represents.

namliam said:
You can then run your report using the Year1 and Year2 columns. You allready know the timeframe for you needed to assign the parameters in Query1, thus you know it is 2002 thru 2005 (and add 2006 as dummy) and you can make the colums be what you need them to be in your report.

You lost me here. Are we renaming the column headings in this technique to match the years or not?

SHADOW

namliam · Nov 27, 2008

shadow9449 said:
There are simpler ways to use criteria in a query, such as giving the user an input box and then referencing that box in the criteria of the query. Either way, same result.

Problem though is referencing forms or having parameters in queries while running a query from code can result in errors, if you put in the parameters 'hardcoded' like this... you KNOW it will work.

shadow9449 said:
I have to figure out a technique for this still. I'm thinking more of creating the colums and leaving them unbound until runtime. If I do that, the extra "columns" can be left unbound and will be blank.

Which is kindoff simular to my dummy column(s) which will be blank/null. Except working with a fixed number of columns and a fixed recordset, I think that is easier... You are going to be bound by the number of columns you can handle anyway.... Unless you bunch in that many controls that you will never have a problem

How much is never tough?? 10?25?50?

shadow9449 said:
Right, but how do you get it to derive the column names? There may be a function to read them into an array or something but I just don't know what that function would be.

The column name... There are two (semi easy) ways of doing this...
1)
Add columns to your query i.e. 2002 as Column1, 2003 as Column2 etc.
2)
Use some code to identify your column names. This code would look something like:

Code:

    Dim qdf As QueryDef
    Dim I As Object
    Set qdf = CurrentDb.QueryDefs("YourCrossQuery")
    For Each I In qdf.Fields
        Debug.Print I.Name
    Next I

This loops all the columns there are in your crosstab, since it is (pre-)fixed... you will know exactly where to search.

There is a third REAL easy way...
Since you have to touch the Crosstab anyway to add the (forced) column names of the years, you allready know the column headings for you report. Pass them to the report somehow (i.e. via the Docmd.Openreport <openargs> argument), then it is a simple matter of updating the labels.

shadow9449 said:
Yes, that is much easier, just a lot less impressive to the user who wants to know what years his data represents.

Easily fixed by updating the labels (see above)

shadow9449 said:
You lost me here. Are we renaming the column headings in this technique to match the years or not?

Yes we are, but you will want to update the labels (see your previous logical statement), for the user will want to know what year he is looking at.

shadow9449 · Nov 28, 2008

namliam said:
Problem though is referencing forms or having parameters in queries while running a query from code can result in errors, if you put in the parameters 'hardcoded' like this... you KNOW it will work.

Ok. Either way is fine

namliam said:
Which is kindoff simular to my dummy column(s) which will be blank/null. Except working with a fixed number of columns and a fixed recordset, I think that is easier... You are going to be bound by the number of columns you can handle anyway.... Unless you bunch in that many controls that you will never have a problem How much is never tough?? 10?25?50?

At this point I would just present this limitation to the user and ask them what the maximum number of years they require would be and stick to it.

namliam said:
The column name... There are two (semi easy) ways of doing this...

Use some code to identify your column names. This code would look something like:

Code:

Dim qdf As QueryDef Dim I As Object Set qdf = CurrentDb.QueryDefs("YourCrossQuery") For Each I In qdf.Fields Debug.Print I.Name Next I

This loops all the columns there are in your crosstab, since it is (pre-)fixed... you will know exactly where to search.

This is exactly what I was looking for!

I'll try it and let you know how it goes.

Thanks again!

SHADOW

shadow9449 · Dec 11, 2008

namliam said:
The column name... There are two (semi easy) ways of doing this...
1)
Add columns to your query i.e. 2002 as Column1, 2003 as Column2 etc.
2)
Use some code to identify your column names. This code would look something like:

Code:

Dim qdf As QueryDef Dim I As Object Set qdf = CurrentDb.QueryDefs("YourCrossQuery") For Each I In qdf.Fields Debug.Print I.Name Next I

This loops all the columns there are in your crosstab, since it is (pre-)fixed... you will know exactly where to search.

There is a third REAL easy way...
Since you have to touch the Crosstab anyway to add the (forced) column names of the years, you allready know the column headings for you report. Pass them to the report somehow (i.e. via the Docmd.Openreport <openargs> argument), then it is a simple matter of updating the labels.

Easily fixed by updating the labels (see above)

Yes we are, but you will want to update the labels (see your previous logical statement), for the user will want to know what year he is looking at.

Mailman (or anyone else reading this):

I tried this and it's pretty easy to force the column headings. My problem here is getting the data into the right place!

Here's where I'm at:

- I have a crosstab query that filters the data just how I want it (thanks to the methods we talked about)

- I have my report with unbound text boxes to show the data. When I open the report I have an OnFormat event that reads the column names of the crosstab query and renames the labels on top of the columns.

The only thing I do NOT have is a way to get the data where it belongs! I tried setting the control source of the text boxes at runtime and I get an error that you can't set a control source at runtime.

How can I force a column name into a crosstab query...?

I'm sure I'm missing a step somewhere...

SHADOW

shadow9449 · Dec 12, 2008

shadow9449 said:
Mailman (or anyone else reading this):

I tried this and it's pretty easy to force the column headings. My problem here is getting the data into the right place!
How can I force a column name into a crosstab query...?

I'm sure I'm missing a step somewhere...

SHADOW

I realized that I can set the data fields at runtime by moving the code into the Open event.

What I need to do to get this report working is to somehow get the totals. How do you set a total field's control source to =Sum(...) when you don't know the name of the field in advance? I tried setting it to "=Sum (" & qdf.fields(I).name & ")". What that does is use the NUMBER 2007 as the total, so if there are 2 values in the section it gives me 4014 as the total! How do I get it to sum the values?

Thanks

SHADOW

namliam · Dec 12, 2008

Can you not set the =Sum at "on open" as well?

shadow9449 · Dec 12, 2008

namliam said:
Can you not set the =Sum at "on open" as well?

Yes, that's what I'm trying to do. But if I don't know the name of the data field, I have this problem:

When I use the syntax: =Sum (" & qdf.fields(I).name & ")", it uses the year as the number I'm trying to add. So if there's one record, it gives me $2007.00 as the result. If there are two records, it gives me $4014.00 and so on. In other words, it's adding the NAME of the field rather than the data in the field.

SHADOW

namliam · Dec 12, 2008

Try putting brackets around it

=Sum ([" & qdf.fields(I).name & "])"

shadow9449 · Dec 12, 2008

namliam said:
Try putting brackets around it

=Sum ([" & qdf.fields(I).name & "])"

It WORKED!!

Dank jewel!!

Groetjes

SHADOW

"In any calendar year" as criteria (1 Viewer)

shadow9449

Registered User.

namliam

The Mailman - AWF VIP

DCrake

Remembered

shadow9449

Registered User.

shadow9449

Registered User.

shadow9449

Registered User.

namliam

The Mailman - AWF VIP

shadow9449

Registered User.

namliam

The Mailman - AWF VIP

shadow9449

Registered User.

namliam

The Mailman - AWF VIP

shadow9449

Registered User.

shadow9449

Registered User.

shadow9449

Registered User.

namliam

The Mailman - AWF VIP

shadow9449

Registered User.

namliam

The Mailman - AWF VIP

shadow9449

Registered User.

Similar threads

Users who are viewing this thread