Update query with loop (1 Viewer)

mounty76

Registered User.
Local time
Today, 14:30
Joined
Sep 14, 2017
Messages
341
Hello!

I have tblCodes this has budget codes in (about 20 records)

I have tblBudget this has 3 fields (Date, Budget Code, Budget)

I need to create an append query so that I can add a new years budget to tblBudget using the budget codes from tblCodes, my problem is that I need to add a month and year next to each budget code then loop this for each month of the year, so it would look something like

Jan 2021 1.1 Travel
Jan 2021 1.2 Expenses
Jan 2021 1.3 Training
Feb 2021 1.1 Travel
Feb 2021 1.2 Expense
Feb 2021 1.3 Training
Mar 2021 1.1 Travel
Mar 2021 1.2 Expenses
Mar 2021 1.3 Training

Etc.....

I'd also need the facility to say what year it is for so I can make a 5 year budget....

Any good suggestions much appreciated!!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:30
Joined
May 7, 2009
Messages
19,169
create a a month table (name it mo)
with field mo (integer)
fill this with 1-12 (month number).

create another table for year (name it yr).
with field yr (long integer)
fill this with the years you want to add, say
2021, 2022 up to 2050?

create a query (cross query/cartesian)

include yr table, mo table and your tblcodes:

insert into tblBudget( [date], [budge code] ) select format(dateserial([yr],[mo],1), "mmm yyyy"), tblcodes.[budget code]
from yr, mo, tblcodes where yr.yr between 2021 and 2025;
 

mounty76

Registered User.
Local time
Today, 14:30
Joined
Sep 14, 2017
Messages
341
Thanks very much arnelgp.

It wasn't appending the budget code, but I changed this in tblBudget to short text instead of a lookup field and it now works perfectly. Thank you so much!
 

mounty76

Registered User.
Local time
Today, 14:30
Joined
Sep 14, 2017
Messages
341
Out of interest.....if I add a new budget code if I run the same query but as an Update query will it then add the new budget code into tblBudget?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:30
Joined
May 7, 2009
Messages
19,169
you need to create an index on your tblbudget (date + [budget code], without duplicate).
so only new budget will be added to your table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:30
Joined
Feb 19, 2002
Messages
42,976
You don't need the extra tables. You can copy the budget for last year and add 1 to the year and append to the budget table. Don't include the autonumber or the amount field in the append query. You want the autonumber to be generated and the amount to be null so you know that no amount has yet been budgeted.
 

Users who are viewing this thread

Top Bottom