period question

I have a form that a user can enter the number of periods to review up to 24.
Current period (Nov 2009) is 23. How can I calculate the correct periods to
sum.
My statement would work something like this; (number of periods to review
Example 4) - (current period Example 23) then add periods (19+20 +21+22).
Easy enough but how do I handle when the period rolls back to 1. I thought
about using a table but the number of variables would make this large. Is
there a betterway?
Thanks

I'm assuming you have records with period number as a field (valid values
1-24) and you need to decide which ones to select and sum based on the user's
input
For this the basic logic would be

periodDiff = currentPeriod - numPeriodsRequested
if periodDiff 0 then
select records where period currentPeriod AND
period = currentPeriod - numPeriodsRequested

else
select records where (period currentPeriod AND period 0) OR
(period = 24 AND period = 24+periodDiff)

end if
"mccloud" wrote:

I have a form that a user can enter the number of periods to review up to 24.
Current period (Nov 2009) is 23. How can I calculate the correct periods to
sum.
My statement would work something like this; (number of periods to review
Example 4) - (current period Example 23) then add periods (19+20 +21+22).
Easy enough but how do I handle when the period rolls back to 1. I thought
about using a table but the number of variables would make this large. Is
there a betterway?
Thanks

Mccloud,
You have asked several vague questions without providing your table and
field names. I expect if you provide some field and table names as well as
enough records and what you expect for results, someone can help you.

If you choose to not provide this information, we can only make wags.
--
Duane Hookom
Microsoft Access MVP


"mccloud" wrote:

I have a form that a user can enter the number of periods to review up to 24.
Current period (Nov 2009) is 23. How can I calculate the correct periods to
sum.
My statement would work something like this; (number of periods to review
Example 4) - (current period Example 23) then add periods (19+20 +21+22).
Easy enough but how do I handle when the period rolls back to 1. I thought
about using a table but the number of variables would make this large. Is
there a betterway?
Thanks

Duane,
Here goes... I have 2 SQL tables "dbo_inv_buy", "dbo_imctlfil_sql" and 1
form "frmitemdtl",
dbo_inv_buy; contains "item_no" and period fields labeled 1,2,3...24. Each
period contains qty sold amount.
dbo_imctlfil_sql; contains "curr_prd"
"frmitemdtl" prompts user for "item_no" and number of periods to review. I
created query "qryitemqtyhist" to pull the records I need for adding period
range. Here's my query.
SELECT dbo_Inv_buy.item_no, [Forms]![frmitemdtl]![txtperiods] AS Periods,
dbo_IMCTLFIL_SQL.curr_prd, dbo_Inv_buy.[1], dbo_Inv_buy.[2], dbo_Inv_buy.[3],
dbo_Inv_buy.[4], dbo_Inv_buy.[5], dbo_Inv_buy.[6], dbo_Inv_buy.[7],
dbo_Inv_buy.[8], dbo_Inv_buy.[9], dbo_Inv_buy.[10], dbo_Inv_buy.[11],
dbo_Inv_buy.[12], dbo_Inv_buy.[13], dbo_Inv_buy.[14], dbo_Inv_buy.[15],
dbo_Inv_buy.[16], dbo_Inv_buy.[17], dbo_Inv_buy.[18], dbo_Inv_buy.[19],
dbo_Inv_buy.[20], dbo_Inv_buy.[21], dbo_Inv_buy.[22], dbo_Inv_buy.[23],
dbo_Inv_buy.[24]
FROM dbo_Inv_buy, dbo_IMCTLFIL_SQL
WHERE (((dbo_Inv_buy.item_no)=[Forms]![frmitemdtl]![cmbitem]) AND
((dbo_IMCTLFIL_SQL.curr_prd)0));
And I'm stuck here on how to calculate which periods to include based on
"periods" requested. The goal is to add together the periods qty's.

"Duane Hookom" wrote:

Mccloud,
You have asked several vague questions without providing your table and
field names. I expect if you provide some field and table names as well as
enough records and what you expect for results, someone can help you.

If you choose to not provide this information, we can only make wags.
--
Duane Hookom
Microsoft Access MVP


"mccloud" wrote:

I have a form that a user can enter the number of periods to review up to 24.
Current period (Nov 2009) is 23. How can I calculate the correct periods to
sum.
My statement would work something like this; (number of periods to review
Example 4) - (current period Example 23) then add periods (19+20 +21+22).
Easy enough but how do I handle when the period rolls back to 1. I thought
about using a table but the number of variables would make this large. Is
there a betterway?
Thanks

Then my previous reply is appropriate. I would normalize the table structure
with a union query like the following where you need to substitute something
for .... that relates Curr_Prd to the period field:

SELECT item_no, [1] as Qty, DateAdd("m", .... ,Curr_Prd) as Mth
FROM dbo_Inv_Buy
UNION ALL
SELECT item_no, [2], DateAdd("m", .... ,Curr_Prd)
FROM dbo_Inv_Buy
UNION ALL
SELECT item_no, [3], DateAdd("m", .... ,Curr_Prd)
FROM dbo_Inv_Buy
UNION ALL
SELECT item_no, [4], DateAdd("m", .... ,Curr_Prd)
FROM dbo_Inv_Buy
UNION ALL
-- etc --
SELECT item_no, [24], DateAdd("m", .... ,Curr_Prd)
FROM dbo_Inv_Buy;

You can then query and sum the Qty column for any date range.


--
Duane Hookom
Microsoft Access MVP


"mccloud" wrote:

Duane,
Here goes... I have 2 SQL tables "dbo_inv_buy", "dbo_imctlfil_sql" and 1
form "frmitemdtl",
dbo_inv_buy; contains "item_no" and period fields labeled 1,2,3...24. Each
period contains qty sold amount.
dbo_imctlfil_sql; contains "curr_prd"
"frmitemdtl" prompts user for "item_no" and number of periods to review. I
created query "qryitemqtyhist" to pull the records I need for adding period
range. Here's my query.
SELECT dbo_Inv_buy.item_no, [Forms]![frmitemdtl]![txtperiods] AS Periods,
dbo_IMCTLFIL_SQL.curr_prd, dbo_Inv_buy.[1], dbo_Inv_buy.[2], dbo_Inv_buy.[3],
dbo_Inv_buy.[4], dbo_Inv_buy.[5], dbo_Inv_buy.[6], dbo_Inv_buy.[7],
dbo_Inv_buy.[8], dbo_Inv_buy.[9], dbo_Inv_buy.[10], dbo_Inv_buy.[11],
dbo_Inv_buy.[12], dbo_Inv_buy.[13], dbo_Inv_buy.[14], dbo_Inv_buy.[15],
dbo_Inv_buy.[16], dbo_Inv_buy.[17], dbo_Inv_buy.[18], dbo_Inv_buy.[19],
dbo_Inv_buy.[20], dbo_Inv_buy.[21], dbo_Inv_buy.[22], dbo_Inv_buy.[23],
dbo_Inv_buy.[24]
FROM dbo_Inv_buy, dbo_IMCTLFIL_SQL
WHERE (((dbo_Inv_buy.item_no)=[Forms]![frmitemdtl]![cmbitem]) AND
((dbo_IMCTLFIL_SQL.curr_prd)0));
And I'm stuck here on how to calculate which periods to include based on
"periods" requested. The goal is to add together the periods qty's.

"Duane Hookom" wrote:

Mccloud,
You have asked several vague questions without providing your table and
field names. I expect if you provide some field and table names as well as
enough records and what you expect for results, someone can help you.

If you choose to not provide this information, we can only make wags.
--
Duane Hookom
Microsoft Access MVP


"mccloud" wrote:

I have a form that a user can enter the number of periods to review up to 24.
Current period (Nov 2009) is 23. How can I calculate the correct periods to
sum.
My statement would work something like this; (number of periods to review
Example 4) - (current period Example 23) then add periods (19+20 +21+22).
Easy enough but how do I handle when the period rolls back to 1. I thought
about using a table but the number of variables would make this large. Is
there a betterway?
Thanks

Bruce,

I'm using a form and query together to get this information. I'm assuming I
would create this in a VB macro and call it from the form but I don't
understand how to select the table fields labeled 1,2,3...24. Can I just use
somethign like

perioddiff = [qrycurprd].[currentPeriod] - [frmitemdtl].[txtperiods]
If perioddiff 0 Then
select [dbo_inv_buy].[1] or [dbo_inv_buy].[2] ect...

Thanks

"Bruce Meneghin" wrote:

I'm assuming you have records with period number as a field (valid values
1-24) and you need to decide which ones to select and sum based on the user's
input
For this the basic logic would be

periodDiff = currentPeriod - numPeriodsRequested
if periodDiff 0 then
select records where period currentPeriod AND
period = currentPeriod - numPeriodsRequested

else
select records where (period currentPeriod AND period 0) OR
(period = 24 AND period = 24+periodDiff)

end if
"mccloud" wrote:

I have a form that a user can enter the number of periods to review up to 24.
Current period (Nov 2009) is 23. How can I calculate the correct periods to
sum.
My statement would work something like this; (number of periods to review
Example 4) - (current period Example 23) then add periods (19+20 +21+22).
Easy enough but how do I handle when the period rolls back to 1. I thought
about using a table but the number of variables would make this large. Is
there a betterway?
Thanks

Bruce,

I'm using a form and query together to get this information. I'm assuming I
would create this in a VB macro and call it from the form but I don't
understand how to select the table fields labeled 1,2,3...24. Can I just use
somethign like

perioddiff = [qrycurprd].[currentPeriod] - [frmitemdtl].[txtperiods]
If perioddiff 0 Then
select [dbo_inv_buy].[1] or [dbo_inv_buy].[2] ect...

Thanks

"Bruce Meneghin" wrote:

I'm assuming you have records with period number as a field (valid values
1-24) and you need to decide which ones to select and sum based on the user's
input
For this the basic logic would be

periodDiff = currentPeriod - numPeriodsRequested
if periodDiff 0 then
select records where period currentPeriod AND
period = currentPeriod - numPeriodsRequested

else
select records where (period currentPeriod AND period 0) OR
(period = 24 AND period = 24+periodDiff)

end if
"mccloud" wrote:

I have a form that a user can enter the number of periods to review up to 24.
Current period (Nov 2009) is 23. How can I calculate the correct periods to
sum.
My statement would work something like this; (number of periods to review
Example 4) - (current period Example 23) then add periods (19+20 +21+22).
Easy enough but how do I handle when the period rolls back to 1. I thought
about using a table but the number of variables would make this large. Is
there a betterway?
Thanks

Bruce,

I'm using a form and query together to get this information. I'm assuming I
would create this in a VB macro and call it from the form but I don't
understand how to select the table fields labeled 1,2,3...24. Can I just use
somethign like

perioddiff = [qrycurprd].[currentPeriod] - [frmitemdtl].[txtperiods]
If perioddiff 0 Then
select [dbo_inv_buy].[1] or [dbo_inv_buy].[2] ect...

Thanks

"Bruce Meneghin" wrote:

I'm assuming you have records with period number as a field (valid values
1-24) and you need to decide which ones to select and sum based on the user's
input
For this the basic logic would be

periodDiff = currentPeriod - numPeriodsRequested
if periodDiff 0 then
select records where period currentPeriod AND
period = currentPeriod - numPeriodsRequested

else
select records where (period currentPeriod AND period 0) OR
(period = 24 AND period = 24+periodDiff)

end if
"mccloud" wrote:

I have a form that a user can enter the number of periods to review up to 24.
Current period (Nov 2009) is 23. How can I calculate the correct periods to
sum.
My statement would work something like this; (number of periods to review
Example 4) - (current period Example 23) then add periods (19+20 +21+22).
Easy enough but how do I handle when the period rolls back to 1. I thought
about using a table but the number of variables would make this large. Is
there a betterway?
Thanks