Alright you formula wizards

K

knowshowrosegrows

Have a table

tblCapacity
fldID
fldEffDate
fldCap

When an ID has it's capacity changed, another record is added with the new
capacity and new effective date. So, ID 555 may have 3 records.

ID EffDate Cap
555 1/1/1995 9
555 4/1/2004 15
555 11/1/2008 11

I need a query that gives me a start date and end date field for each ID

"StartDate" = EffDate

If EffDate = MaxOfEffDate
Then "EndDate" = [EffDate] - 1
Else "EndDate" is Null

Does this make sense? Can someone explain to me how to make this query as
if I were a 9 year old?
 
D

Dorian

SELECT ID, MIN(EFFDATE) As First, MAX(EFFDATE) As Last FROM tblCapacity GROUP
BY ID

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
B

Beetle

If your table has the following records;

ID EffDate Cap
555 1/1/1995 9
555 4/1/2004 15
555 11/1/2008 11

then the following query;

SELECT ID, EffDate AS StartDate,
IIf(DMin("[EffDate]","tblCapacity","[EffDate]>#" & [EffDate] &
"#")>0,DateAdd("d",-1,DMin("[EffDate]","tblCapacity","[EffDate]>#" &
[EffDate] & "#")),Null) AS EndDate, Cap
FROM tblCapacity;

will return the following records;

ID StartDate EndDate Cap
555 1/1/1995 3/31/2004 9
555 4/1/2004 10/31/2008 15
555 11/1/2008 11

If you're not sure how to create it, you can open the query design grid,
switch to SQL view and copy and paste this. You will need to correct the
line wrap, an possibly the field names.
 
K

knowshowrosegrows

You are the best.

The query is not quite working.

Here is my sql for Prm_Code "44033308504A":

SELECT tblCapacity.Prm_Code, tblCapacity.EffectiveDate AS StartDate,
IIf(DMin("[EffectiveDate]","tblCapacity","[EffectiveDate]>#" &
[EffectiveDate] &
"#")>0,DateAdd("d",-1,DMin("[EffectiveDate]","tblCapacity","[EffectiveDate]>#" & [EffectiveDate] & "#")),Null) AS EndDate, tblCapacity.Cap
FROM tblCapacity
WHERE (((tblCapacity.Prm_Code)="44033308504A"));

Here is the original data in tblCapacity:

Prm_Code EffectiveDate Cap
44033308504A 1/1/1995 15
44033308504A 4/1/2004 20

Here is the result I am currently getting:

Prm_Code StartDate EndDate Cap
44033308504A 1/1/1995 1/31/1995 15
44033308504A 4/1/2004 4/30/2004 20

Here is the result I am looking for:

Prm_Code StartDate EndDate Cap
44033308504A 1/1/1995 3/31/2004 15
44033308504A 4/1/2004 Null 20
--
Thanks

You all are teaching me so much


Beetle said:
If your table has the following records;

ID EffDate Cap
555 1/1/1995 9
555 4/1/2004 15
555 11/1/2008 11

then the following query;

SELECT ID, EffDate AS StartDate,
IIf(DMin("[EffDate]","tblCapacity","[EffDate]>#" & [EffDate] &
"#")>0,DateAdd("d",-1,DMin("[EffDate]","tblCapacity","[EffDate]>#" &
[EffDate] & "#")),Null) AS EndDate, Cap
FROM tblCapacity;

will return the following records;

ID StartDate EndDate Cap
555 1/1/1995 3/31/2004 9
555 4/1/2004 10/31/2008 15
555 11/1/2008 11

If you're not sure how to create it, you can open the query design grid,
switch to SQL view and copy and paste this. You will need to correct the
line wrap, an possibly the field names.

--
_________

Sean Bailey


knowshowrosegrows said:
Have a table

tblCapacity
fldID
fldEffDate
fldCap

When an ID has it's capacity changed, another record is added with the new
capacity and new effective date. So, ID 555 may have 3 records.

ID EffDate Cap
555 1/1/1995 9
555 4/1/2004 15
555 11/1/2008 11

I need a query that gives me a start date and end date field for each ID

"StartDate" = EffDate

If EffDate = MaxOfEffDate
Then "EndDate" = [EffDate] - 1
Else "EndDate" is Null

Does this make sense? Can someone explain to me how to make this query as
if I were a 9 year old?
Click to expand...
Click to expand...
 
B

Beetle

Sorry about that. I forgot to add the ID (Prm_Code) value as criteria
in the DMin statement. Prm_Code appears to be a Text data type, so
modify your query as follows;

SELECT tblCapacity.Prm_Code, tblCapacity.EffectiveDate AS StartDate,
IIf(DMin("[EffectiveDate]","tblCapacity","[EffectiveDate]>#" &
[EffectiveDate] &
"# And [Prm_Code]=""" & [Prm_Code] &
"""")>0,DateAdd("d",-1,DMin("[EffectiveDate]","tblCapacity","[EffectiveDate]>#"
& [EffectiveDate] & "# And [Prm_Code]=""" & [Prm_Code] & """")),Null) AS
EndDate, tblCapacity.Cap
FROM tblCapacity
WHERE (((tblCapacity.Prm_Code)="44033308504A"));

--
_________

Sean Bailey


knowshowrosegrows said:
You are the best.

The query is not quite working.

Here is my sql for Prm_Code "44033308504A":

SELECT tblCapacity.Prm_Code, tblCapacity.EffectiveDate AS StartDate,
IIf(DMin("[EffectiveDate]","tblCapacity","[EffectiveDate]>#" &
[EffectiveDate] &
"#")>0,DateAdd("d",-1,DMin("[EffectiveDate]","tblCapacity","[EffectiveDate]>#" & [EffectiveDate] & "#")),Null) AS EndDate, tblCapacity.Cap
FROM tblCapacity
WHERE (((tblCapacity.Prm_Code)="44033308504A"));

Here is the original data in tblCapacity:

Prm_Code EffectiveDate Cap
44033308504A 1/1/1995 15
44033308504A 4/1/2004 20

Here is the result I am currently getting:

Prm_Code StartDate EndDate Cap
44033308504A 1/1/1995 1/31/1995 15
44033308504A 4/1/2004 4/30/2004 20

Here is the result I am looking for:

Prm_Code StartDate EndDate Cap
44033308504A 1/1/1995 3/31/2004 15
44033308504A 4/1/2004 Null 20
--
Thanks

You all are teaching me so much


Beetle said:
If your table has the following records;

ID EffDate Cap
555 1/1/1995 9
555 4/1/2004 15
555 11/1/2008 11

then the following query;

SELECT ID, EffDate AS StartDate,
IIf(DMin("[EffDate]","tblCapacity","[EffDate]>#" & [EffDate] &
"#")>0,DateAdd("d",-1,DMin("[EffDate]","tblCapacity","[EffDate]>#" &
[EffDate] & "#")),Null) AS EndDate, Cap
FROM tblCapacity;

will return the following records;

ID StartDate EndDate Cap
555 1/1/1995 3/31/2004 9
555 4/1/2004 10/31/2008 15
555 11/1/2008 11

If you're not sure how to create it, you can open the query design grid,
switch to SQL view and copy and paste this. You will need to correct the
line wrap, an possibly the field names.

--
_________

Sean Bailey


knowshowrosegrows said:
Have a table

tblCapacity
fldID
fldEffDate
fldCap

When an ID has it's capacity changed, another record is added with the new
capacity and new effective date. So, ID 555 may have 3 records.

ID EffDate Cap
555 1/1/1995 9
555 4/1/2004 15
555 11/1/2008 11

I need a query that gives me a start date and end date field for each ID

"StartDate" = EffDate

If EffDate = MaxOfEffDate
Then "EndDate" = [EffDate] - 1
Else "EndDate" is Null

Does this make sense? Can someone explain to me how to make this query as
if I were a 9 year old?
Click to expand...
Click to expand...
Click to expand...
 
K

knowshowrosegrows

You are a wizard!

Here - have a some homemade holiday cookies.
--
Thanks

You all are teaching me so much


Beetle said:
Sorry about that. I forgot to add the ID (Prm_Code) value as criteria
in the DMin statement. Prm_Code appears to be a Text data type, so
modify your query as follows;

SELECT tblCapacity.Prm_Code, tblCapacity.EffectiveDate AS StartDate,
IIf(DMin("[EffectiveDate]","tblCapacity","[EffectiveDate]>#" &
[EffectiveDate] &
"# And [Prm_Code]=""" & [Prm_Code] &
"""")>0,DateAdd("d",-1,DMin("[EffectiveDate]","tblCapacity","[EffectiveDate]>#"
& [EffectiveDate] & "# And [Prm_Code]=""" & [Prm_Code] & """")),Null) AS
EndDate, tblCapacity.Cap
FROM tblCapacity
WHERE (((tblCapacity.Prm_Code)="44033308504A"));

--
_________

Sean Bailey


knowshowrosegrows said:
You are the best.

The query is not quite working.

Here is my sql for Prm_Code "44033308504A":

SELECT tblCapacity.Prm_Code, tblCapacity.EffectiveDate AS StartDate,
IIf(DMin("[EffectiveDate]","tblCapacity","[EffectiveDate]>#" &
[EffectiveDate] &
"#")>0,DateAdd("d",-1,DMin("[EffectiveDate]","tblCapacity","[EffectiveDate]>#" & [EffectiveDate] & "#")),Null) AS EndDate, tblCapacity.Cap
FROM tblCapacity
WHERE (((tblCapacity.Prm_Code)="44033308504A"));

Here is the original data in tblCapacity:

Prm_Code EffectiveDate Cap
44033308504A 1/1/1995 15
44033308504A 4/1/2004 20

Here is the result I am currently getting:

Prm_Code StartDate EndDate Cap
44033308504A 1/1/1995 1/31/1995 15
44033308504A 4/1/2004 4/30/2004 20

Here is the result I am looking for:

Prm_Code StartDate EndDate Cap
44033308504A 1/1/1995 3/31/2004 15
44033308504A 4/1/2004 Null 20
--
Thanks

You all are teaching me so much


Beetle said:
If your table has the following records;

ID EffDate Cap
555 1/1/1995 9
555 4/1/2004 15
555 11/1/2008 11

then the following query;

SELECT ID, EffDate AS StartDate,
IIf(DMin("[EffDate]","tblCapacity","[EffDate]>#" & [EffDate] &
"#")>0,DateAdd("d",-1,DMin("[EffDate]","tblCapacity","[EffDate]>#" &
[EffDate] & "#")),Null) AS EndDate, Cap
FROM tblCapacity;

will return the following records;

ID StartDate EndDate Cap
555 1/1/1995 3/31/2004 9
555 4/1/2004 10/31/2008 15
555 11/1/2008 11

If you're not sure how to create it, you can open the query design grid,
switch to SQL view and copy and paste this. You will need to correct the
line wrap, an possibly the field names.

--
_________

Sean Bailey


:

Have a table

tblCapacity
fldID
fldEffDate
fldCap

When an ID has it's capacity changed, another record is added with the new
capacity and new effective date. So, ID 555 may have 3 records.

ID EffDate Cap
555 1/1/1995 9
555 4/1/2004 15
555 11/1/2008 11

I need a query that gives me a start date and end date field for each ID

"StartDate" = EffDate

If EffDate = MaxOfEffDate
Then "EndDate" = [EffDate] - 1
Else "EndDate" is Null

Does this make sense? Can someone explain to me how to make this query as
if I were a 9 year old?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Query Help 6
quick query question 3
lookup date in the interval 4
Access 2000 Query very slow compared to SQL Query Analyzer 5
Query using date range entered by user 2
last date list not working 1
Need query help 14
Tricky Calculation Question 9

Top