By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Need to query data for same days/months but different years

jaccess
P: 26
Hello,

I am fairly new to Access and have been working on this problem for several days now. I don't suspect that the answer I am looking for should be as elucive as it has been.

I will attempt to explain the purpose of the query I am trying to make.

I am trying to display orders for a specific date range over a 3 year period based on a single date range entered in a form.

The criteria used in my query is as follows:

Between [FORMS]![frmDate]![Date1] And [FORMS]![frmDate]![Date2]

Which works fine and retrieves that data for that specic date range for that year. I have been reading about and experimenting with DateAdd(), and DateSerial but to no avial.

Here is the outcome I am trying to achieve:

On the form I enter my start date as 04/15/2006 and end date as 05/19/2006

and would like to display

Day/Month 04/05 05/06 06/07

15 Apr 5522 555 0
16 Apr 556 4 0
17 Apr 0 0 0
18 Apr 465 54 0
Etc
19 May 454 54 0


Any help would be greatly appreciated.
Apr 11 '07 #1
Share this Question
Share on Google+
7 Replies


Denburt
Expert 100+
P: 1,356
and would like to display
Day/Month 04/05 05/06 06/07
Day(WhateverDate) & "/" & Month(WhateverDate)


I don't understand the following (maybe just a long day).
15 Apr 5522 555 0
16 Apr 556 4 0
17 Apr 0 0 0
18 Apr 465 54 0
Etc
19 May 454 54 0
Apr 11 '07 #2

jaccess
P: 26
Day(WhateverDate) & "/" & Month(WhateverDate)


I don't understand the following (maybe just a long day).
Thank you for your assistance!

I am truly sorry I did not make my question/example as clear as I should have.

I am trying to pull similar data (orders) from different tables (tbl2005, tbl2006, tbl2007) based on a fiscal date range.

For example I need to show daily orders for my company's fiscal month of October which is actually Sep. 16th to Oct 13.

I have another table set up with the fiscal year which I know should link the yearly tables together to get the same date range but a different year.

So if I enter Sep 16 as my start date on my form and Oct 13 as the end date I would like the query to retrieve all of the order from Sep 16 to Oct 13 for each year.

-------------- 2005 2006 2007
------------------------------------------------
Sep 16 ------------ (Orders)
Sep 17
Sep18
(all the way to ..)
Oct 13

I this explains a bit more of what I am trying to do. If you need any other information please let me know.
Apr 12 '07 #3

Denburt
Expert 100+
P: 1,356
I am not quite sure why you have each year in a different table in most cases you should be able to put all the data in one table and have less issues.

One idea using your current structure is to use a union query which would need to be designed in SQL you will not be able to use the design grid so it may be a little more complicated. When i first started using Union Queries I used the design grid to build each SQL statement then view the sql and copy paste into my union query. Each one will have to have the same number of fields. You may want to do a little research on union queries.

Union Query SQL:
Expand|Select|Wrap|Line Numbers
  1. Select Day(WhateverDate) & "/" & Month(WhateverDate) as WhateverNameUDecide From Table tbl2005
  2.  
  3. Union Select Day(WhateverDate) & "/" & Month(WhateverDate) as WhateverNameUDecide From Table tbl2006
  4.  
  5. Union Select Day(WhateverDate) & "/" & Month(WhateverDate) as WhateverNameUDecide From Table tbl2007
  6.  
However every year you will need to add to it. See the problem with breaking it down this way? That is why I stated that one table would probably be the best way to go. I am sure there must be other ways maybe someone else will pipe up and offer a better suggestion.
Apr 12 '07 #4

jaccess
P: 26
Once again thank you for your patience,

I separated each year into a different table as I was having trouble with filtering a specific years data I believe. As I have been working on this for about a week it made sense at the time. The order data actually is all on one table and I filtered it into 3 tables (one for each year). So it is entirely possible to query the data from a single table if it will help.

I have also since made a new field in my tblFiscalMonth (a linked excel file) for the short year (04/15 for April 15) in text data type. I don't think being in text format matters much as it is linked to the full fiscal date, fiscal year start date and almost every other format.

At this point I am able to get the information I want the way I have it set up now.
(tbl2005, tbl2006, tbl2007, tblFiscalMonth) linked by the SHORTDATE field form the FiscalMonth table. (04/15 for 04/15/2006 or April 15 etc) My problem is that it is now repeating the information several times.

In my query I enter the short date start (04/15) and end date(05/19) for the fiscal month of May and I get 105 records returned instead of just the 35 I figure it should. It starts at 04/15 goes to 05/19 then repeats from 04/15 to 05/19 x3.

This may be an entirely new issue now. If you want to see any of the code or need more detail please let me know. I understand it must be frustrating getting all of the info you need to help when I don't know what to provide/ or how to describe what is going on.

I will take a look at the other info you had suggested an see if it help too.

Thanks!
Apr 12 '07 #5

Denburt
Expert 100+
P: 1,356
Yeah without having all of the information it is difficult. When you look at your query in design view go to the properties window and look for the "Unique records" Property this may resolve your issue.
Apr 12 '07 #6

jaccess
P: 26
Yeah without having all of the information it is difficult. When you look at your query in design view go to the properties window and look for the "Unique records" Property this may resolve your issue.

Success!!!

Selecting "Unique Values" in the properties window solved my problem.

Thank you very much for all of your help and time Denburt.

If this is ever an issue for anyone else right click in the design view of your query and select properties, then make your changes.
Apr 13 '07 #7

Denburt
Expert 100+
P: 1,356
You are quite welcome glad I could be of assistance, and thanks for helping to define my answer a little better.
Apr 13 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.