Need some help with a query

  • Thread starter Thread starter Elisa
  • Start date Start date
E

Elisa

I am fairly new to Access and I'm having trouble with a
query. I'm working with a table that has a start date and
an end date for the timeframes that different clients have
been effective with my company. I'm trying to do a query
that creates a record for each month within the range of
the start and end dates.
I'd appreciate any help.

Thanks!
 
Elisa,

Need a little more information about what you want the output of the
query to look like (an example would be good). Give us a sample of
what 1 or 2 of your records look like, and then what you would like
the output of your query to look like.

--
HTH

Dale Fye


I am fairly new to Access and I'm having trouble with a
query. I'm working with a table that has a start date and
an end date for the timeframes that different clients have
been effective with my company. I'm trying to do a query
that creates a record for each month within the range of
the start and end dates.
I'd appreciate any help.

Thanks!
 
I am fairly new to Access and I'm having trouble with a
query. I'm working with a table that has a start date and
an end date for the timeframes that different clients have
been effective with my company. I'm trying to do a query
that creates a record for each month within the range of
the start and end dates.
Click to expand...

An auxiliary table would help a lot here. Create a table tMonths, with
only one date/time field TheMonth; manually (or use Excel's fill down
and copy and paste) fill it with Date/Time fields for the first day of
each month in the relevant time range.

Now create a Query by adding your table and tMonths to the query
design window; *don't* include a Join line. As a criterion on TheMonth
put
= [yourtable].[Start Date] AND <= [yourtable].[End Date]
Click to expand...

Include whatever fields you want to see, and TheMonth.
 
Dale:

Thanks for responding.

Here is a sample of the table I'm working with:

MEMBERID OPFROMDT COCODE CURRHIST OPTHRUDT

1832108OD 11/01/1996 GO3 H 12/31/1998

1832108OD 01/01/1999 GO3 C 06/01/2000

1325481SL 09/01/2000 CIB C 11/30/2000


Here is what I want the output of my query to look like.

COCODE MidMonth MCnt
CIB 09/15/2000 4083
CIB 10/15/2000 3868
CIB 11/15/2000 3715

What I want my query to do is, for each MEMBERID give me a
separate record for all of the months within the OPFROMDT
and OPTHRUDT range. Once I have records for all of the
months in the ranges then I can do a count of the months
and sort by COCODE.
 
Is there a 1-1 relationship between MemberID and CoCode? I'm also not
sure where you get your MCnt value from.

Here is some guidance similiar to what John provided, but with more
detail. Also, this returns the month value formatted as YYYY MMM
rather than mm/15/yyyy.

Create a new table (tblNumbers) in your database. This table should
have one field intValue (LongInteger), and the table should have 10
records in it 0-9. Once you've done this, create a new query
(qryNumbers). In the SQL view, paste the following query. This query
will actually create a recordset that has the numbers from 0 thru
9999. If the length between OpFromDt and OpThruDt is more less than
1000 days you can remove the referenc to the thousands table

SELECT [Thousands].[intValue]*1000
[Hundreds].[intValue]*100+
[Tens].[intValue]*10+
[Ones].[intValue] AS intNumber
FROM tblNumbers AS Thousands
, tblNumbers AS Hundreds
, tblNumbers AS tens
, tblNumbers AS ones

Now, create another query to identify each of the customers by month.
This query joins your original table to qryNumbers with a Cartesian
join (no fields in common). What it essentially does is creates a
recordset that contains 9999 records for each of record in your
original table (for each record in your table, it creates a new record
in the query result set by adding the value in the query to the
OpFromDt). The where clause then restricts this down to one record
for each day between the startdate and enddate. The Format() function
allows you to format the date with only the year and month, allowing
us to group by the YearMonth field and get one record for each record
in your table, for each month they were a client.

The reason I grouped by the dateadd() function twice is that in order
to sort it my Year/Month in the proper order, you have to include the
numeric representation of the year and month, otherwise, it would sort
by year, then by month alphabetically.

SELECT yourTable.CoCode
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy mmm')
AS YearMonth
FROM yourTable, qryNumbers
WHERE DateAdd('d',[intNumber],[OpFromDt]))<=[OpThruDt]))
GROUP BY yourTable.CoCode
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy
mmm')
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy
mm')
ORDER BY yourTable.CoCode
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy
mm');

--
HTH

Dale Fye


Dale:

Thanks for responding.

Here is a sample of the table I'm working with:

MEMBERID OPFROMDT COCODE CURRHIST OPTHRUDT

1832108OD 11/01/1996 GO3 H 12/31/1998

1832108OD 01/01/1999 GO3 C 06/01/2000

1325481SL 09/01/2000 CIB C 11/30/2000


Here is what I want the output of my query to look like.

COCODE MidMonth MCnt
CIB 09/15/2000 4083
CIB 10/15/2000 3868
CIB 11/15/2000 3715

What I want my query to do is, for each MEMBERID give me a
separate record for all of the months within the OPFROMDT
and OPTHRUDT range. Once I have records for all of the
months in the ranges then I can do a count of the months
and sort by COCODE.
 
I created the auxiliary table and it worked except for
when the end date is blank because the client is still
effective with my company.
-----Original Message-----
I am fairly new to Access and I'm having trouble with a
query. I'm working with a table that has a start date and
an end date for the timeframes that different clients have
been effective with my company. I'm trying to do a query
that creates a record for each month within the range of
the start and end dates.
Click to expand...

An auxiliary table would help a lot here. Create a table tMonths, with
only one date/time field TheMonth; manually (or use Excel's fill down
and copy and paste) fill it with Date/Time fields for the first day of
each month in the relevant time range.

Now create a Query by adding your table and tMonths to the query
design window; *don't* include a Join line. As a criterion on TheMonth
put
= [yourtable].[Start Date] AND <= [yourtable].[End Date]
Click to expand...

Include whatever fields you want to see, and TheMonth.


.
Click to expand...
 
Elisa,

To account for the situation you mentioned in your note to John,
modify the query as follows:

SELECT yourTable.CoCode
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy mmm')
AS YearMonth
FROM yourTable, qryNumbers
WHERE DateAdd('d',[intNumber],[OpFromDt])<=NZ([OpThruDt], Date())
GROUP BY yourTable.CoCode
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy mmm')
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy mm')
ORDER BY yourTable.CoCode
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy mm');

--
HTH

Dale Fye


Is there a 1-1 relationship between MemberID and CoCode? I'm also not
sure where you get your MCnt value from.

Here is some guidance similiar to what John provided, but with more
detail. Also, this returns the month value formatted as YYYY MMM
rather than mm/15/yyyy.

Create a new table (tblNumbers) in your database. This table should
have one field intValue (LongInteger), and the table should have 10
records in it 0-9. Once you've done this, create a new query
(qryNumbers). In the SQL view, paste the following query. This query
will actually create a recordset that has the numbers from 0 thru
9999. If the length between OpFromDt and OpThruDt is more less than
1000 days you can remove the referenc to the thousands table

SELECT [Thousands].[intValue]*1000
[Hundreds].[intValue]*100+
[Tens].[intValue]*10+
[Ones].[intValue] AS intNumber
FROM tblNumbers AS Thousands
, tblNumbers AS Hundreds
, tblNumbers AS tens
, tblNumbers AS ones

Now, create another query to identify each of the customers by month.
This query joins your original table to qryNumbers with a Cartesian
join (no fields in common). What it essentially does is creates a
recordset that contains 9999 records for each of record in your
original table (for each record in your table, it creates a new record
in the query result set by adding the value in the query to the
OpFromDt). The where clause then restricts this down to one record
for each day between the startdate and enddate. The Format() function
allows you to format the date with only the year and month, allowing
us to group by the YearMonth field and get one record for each record
in your table, for each month they were a client.

The reason I grouped by the dateadd() function twice is that in order
to sort it my Year/Month in the proper order, you have to include the
numeric representation of the year and month, otherwise, it would sort
by year, then by month alphabetically.

SELECT yourTable.CoCode
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy mmm')
AS YearMonth
FROM yourTable, qryNumbers
WHERE DateAdd('d',[intNumber],[OpFromDt]))<=[OpThruDt]))
GROUP BY yourTable.CoCode
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy
mmm')
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy
mm')
ORDER BY yourTable.CoCode
, Format(DateAdd('d',[intNumber],[OpFromDt]),'yyyy
mm');

--
HTH

Dale Fye


Dale:

Thanks for responding.

Here is a sample of the table I'm working with:

MEMBERID OPFROMDT COCODE CURRHIST OPTHRUDT

1832108OD 11/01/1996 GO3 H 12/31/1998

1832108OD 01/01/1999 GO3 C 06/01/2000

1325481SL 09/01/2000 CIB C 11/30/2000


Here is what I want the output of my query to look like.

COCODE MidMonth MCnt
CIB 09/15/2000 4083
CIB 10/15/2000 3868
CIB 11/15/2000 3715

What I want my query to do is, for each MEMBERID give me a
separate record for all of the months within the OPFROMDT
and OPTHRUDT range. Once I have records for all of the
months in the ranges then I can do a count of the months
and sort by COCODE.
 
I created the auxiliary table and it worked except for
when the end date is blank because the client is still
effective with my company.
Click to expand...

ok... you didn't say that <g>!

Try
= [yourtable].[Start Date] AND ([TheMonth]<= [yourtable].[End Date] OR yourtable.[End Date] IS NULL)
Click to expand...
 
Back
Top