Query ran from date entry in table

[kdoran]

I am trying to run a query that would use a date in a simple table that has 12 entries, the date for the first of each month.

I want to use that date say 03/01/06 from the table and run a guery to find the entries in another table that have dates in that month.
I get how to use between date()-30... but not sure how to use the first table date entry to get the dates for the second table without having to put the actual date in the criteria.

thanks in advance,

Kelly

 

[cranebill]

Select table2.*
from table1, table2
where month(table1.date) = month(table2.date)
and year(table1.date) = year(table2.date);

This is using year to... if your just looking month specific remove the 'and' statement

 

[kdoran]

Ok That works out great. Still have a couple of issues. Is it possible to have more than one date in table1 and key off just one date, cause if I do it pulls all of them in the query.

Here is what table1 looks like

MonthlID MonthlyDate1ID
1 1/1/2006
2 2/1/2006
3 3/1/2006
4 4/1/2006
5 5/1/2006
6 6/1/2006
7 7/1/2006
8 8/1/2006
9 9/1/2006
10 10/1/2006
11 11/1/2006
12 12/1/2006

How do I handle this? Do i need to have multiple tables with the individual date in just one field, instead of just 1 table? I would rather not do that if I have to.

Thanks,

Kelly

 

[cranebill]

What are you using to execute this query? Does this query just populate a report or something? If you are using a button in an access application you can use a combo box and reference the criteria that way. Or if you elaborate on the application it may give a better idea of how to approach this.

 

[kdoran]

Yeah I will use a button to populate a page/report. Here is a little background info. My corp (GP) has yearly boiler audits when encompass many areas. What I am trying to build is a page/report so the Utilities superintendent can go a check to see how each of the areas are coming along on their required audit checks. There are weekly, monthly, quarterly, half yearly, yearly, 2yr, 3yr, 5yr, 7yr, 10yr check requirements. I have to be able to show a report for all of these. I just started with monthly. The superintendent will report to the mill manager weekly in there meeting.

The checks are keyed of a yes/no box for example criticalmotors. And we are look at March.
These are done monthly and there will be a query ran to count how many yes checks there are. So it will show 42. Then I will run the query you have given and then count how many entries off the date and then this will show right below the 42 to show how many were done.

I also am trying to figure out how to do this weekly as well.

Is this the type of elaberation you were looking for? If not let me know and will give more info.

Thanks for this.

Kelly

 

[cranebill]

If your using a button to run the sql then create a combobox to use as criteria, have the sql reference the combobox for the month as criteria to generate the report....

I know there is a way to do this... however my access skills with buttons etc has diminished as i just use SQL now and use this to keep my sql skills in check.

 

[kdoran]

That is where I have a problem as my sql experience, well calling it limited is being nice. I will try though. If you have a starting point that would be great.

Also, is there a way to key off the week, or is it possible to convert the date into a general number the way excel can to get data for the weekly ones.

 

[PHV]

Have a look at the Format or DatePart functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kdoran]

When using the datepart function or the above code cranebill described to get a weeks worth of info similar to the way between Date()-(7) works? I have tried a few ways but no luck. This also would solve my 6month issue etc...



(cranebill, On the combo box, I see what you are saying)

 

[cranebill]

When you say week, how are you wanting to break it up...

for example if the billing week ends on Saturday, then April 1 of this year would have 1 day in the first billing week.

 

[kdoran]

I am looking at jan thru dec, and mon thru sunday. Going off the first monday of the year. What I would like to do is enter a date for the first monday of the year, roughly 52 weeks. I am not too familiar with while loop (but this may get 52 weeks of the year)

that is how i am trying to break it up but open to any ideas.

 

[kdoran]

Back from vacation... Any ideas how to do what this (last post).

 

[cranebill]

So you want to show data for all 52 weeks, or just one week at a time?

 

[cranebill]

You could do this:

Select *
from table2
where DatePart('ww',Date,2,1) = 1
and DatePart('yyyy',Date,2,1) = 2006

This will give you data for first week

DatePart('ww',Table1.Date,2,1)
in this the ,2 represents Monday as starting day of week and ,1 represents Jan 1st as starting date...

Please see

http://www.techonthenet.com/access/functions/date/datepart.php

 

[kdoran]

I am looking to show data just for each week of the year (52), one week at a time. The report will most likely be grouped with a button (12 for the year). For example

Lets say the date is Jan 15th. The 1st button will run multiple queries for Jan. This will include the weekly queries (even though only two weeks gone by that data will show but there are more weeks but will not pull anything up as the dates have not happened). Also the monthly queries, quarterly and yearly, etc.. This will show if any data has been entered and what still needs to be done.

 

[cranebill]

So what exactly are you doing with the data?

Are you going to sum something, count something, or just list records?

What are the fields you will be using?

 

[kdoran]

I am going to count the entries. just below on the report will be a querie that show the number that should be done.
This will show that 100 of out 105 are done.

I am using the date field and a yes/no box for the other part.

 

[cranebill]

How about this...

SELECT DatePart('ww',YourDate,2,1) AS Week , =Sum(IIF([Your Yes/no field],1,0)) AS [Count]
FROM Your_table
GROUP BY DatePart('ww',YourDate,2,1), Table1.[Yes]
HAVING ((([Your Yes/no field])=-1));

 

[kdoran]

I will give this a try and let you know

thanks