date filter between 2 dates

[batman1056]

I have a querie that has a list of items sold over 2 years, the fields in the querie are:
Item
Date (date sold)
Cost

I am trying to create a filter that shows the data in set time periods, so in my querie, I have 5 expressions that will be marked Apr-Jun04 Jul-Sep04 Oct-Dec04 Jan-Mar05

what I would to do is find a way to put my own dates in the above areas so that I will only get the count of items sold in those areas.

The next step will be to have another filter that will seperate those items that costed more than £400 in a seperate coloum...

Any help appreciated.

 

[Mile-O]

And what happens when you want to query on April 2005 to whenever?

CLUE: The answer is not I'll cross that bridge when I come to it!

 

[batman1056]

April 05 onward is open..so far, but I could just continue adding the dates..

 

[Mile-O]

Can you show what you have now? I should be able to suggest a workable solution.

 

[batman1056]

I have created a sample database, its got a few items in it, and 1 querie,
The Query shows you that I am trying to cound the number of items that appear in a speciic date period, i.e.
I haev 400 items over 2 years, so I create a Querie that picks out specific dates and counts how many items appear in each period.
I want to be able to have numerious Expressions that show me specific `counts` from the dates given.

 

[Mile-O]

I can't see what you were trying to do but I think this is what you want.

 

[batman1056]

thanks, I can work with that, now my question is: how does it define the dates? I am not ofay with the date command, so can you tell me how it defines the period i,e April-June 04 etc.., IF I wanted to make the period March-May (whcih I don`t do I just add-1)?>

Thanks for this.

 

[Mile-O]

Move to the modules tab and have a look at the function there.

 

[batman1056]

Almost.. Thanks for the Moduel, I must get a decent book to read up on for access,.. Any recomendations.?

I have got my list, BUT.. If I have data in 2002,2003,2004 when it groups the dates it also groups the data, so in my query it lists them like so:-
Quarter CountOfCompany Name
Apr-04 to Jun-04 131
Apr-06 to Jun-06 2
Apr-41 to Jun-41 1
Apr-42 to Jun-42 1
Apr-84 to Jun-84 1
Jan-04 to Mar-04 20
Jul-04 to Sep-04 45
Oct-03 to Dec-03 4

Is it possible to put it in date order?

Chears

 

[Mile-O]

Include the date in the query, sort it as Ascending, and then uncheck its visibility.

As for book recommendations, I don't really know. I have the Access Developers 2000 and VB & VBA In a Nutshell the former being advanced (-ish) while the latter is a reference guide to the language.

There are plenty of book recommendations if you use the search function.

 

[batman1056]

Great.. I have done it.
I have created two queries one that shows the totals count for those udner 300 and anther that shows those over 300...

Just a little thought, is it possible to show these on the same querie?
so I`d have the periods i.e April-June and then the next colum would have the count for those under 300 the next colum would have those over 300 (if no data was in that period it would be blank.

Many thanks again for this. I can manage with what you have done so far, just thought I`d try my luck

 

[Mile-O]

Create a new query, and switch to SQL View.

You need the SQL statements of both queries an you join them with the word UNION.

i.e.

SELECT *
FROM MYTable1
UNION
SELECT *
FROM MyTable2;

Both queries must have equal number of fields and be of the same data types when joining them. You can create a new field in each query called, for example, Crit.

i.e.

Crit: "<300"

Two things:

• What do you do if something is equal to 300?
• You could just use an IIf() function to determine whether the value is above or below 300 and fill the field accordingly - no need to join two queries. No need, in fact, for two queries.