period question

[mccloud]

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 Hookom]

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.

 

[mccloud]

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]

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

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.

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.

 

[mccloud]

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