Append Query

[CoffeeGuru]

Apologies if this is so easy in Excel I'd do this in VBA, in Access I haven't a clue but assume SQL can do this.
I am trying to get a user to enter a year and week number and get Access to append this to Table "A01 Date"

This I have managed by creating a Query

Qry
A01 Enter Date 1
Field Y: [Enter Year (format yyyy)] APPEND TO Year
Field W: [Enter week (format #)] APPEND TO Week

Year and Week are from Table A01 Date
What I am currently doing is writing a whole series of queries that then take week-1 then -2 then -3 etc

Qry
A02 date 1 on Date SET
Field Year Table A01 Date APPEND TO Year
Field w:[Week]-1 Table A01 Date APPEND TO Week

Qry
A03 date 1 on Date SET W1 -1
Field Year Table A01 Date APPEND TO Year
Field w:[Week]-1 Table APPEND TO Year

My question is how can I ask the user to enter the number of historic weeks to work with 1 -52
and make a loop to append the results to a table.

If this makes no sense please prompt me for more information

----
Update
I have noticed whilst playing with this the long way, that I need to allow for year 2013 week 1 minus 1 = year 2013 Week -2 Hmmm
Need to make sure that this goes to 2012 Week 52

I think I need to have a field that is a real date to do this :banghead:

 

[JHB]

Can you explain what your purpose is with the above. What is it you want to perform, I know you want to add to a table but for what use?

 

[CoffeeGuru]

Hi JBH

OK I'll keep this as brief as possible, if further details are needed I'll fill in the blanks

I am attempting to create some sort of forecasting tool. Historically I have been using Excel as thats where my best knowledge is and what I do best. But this is a new job and the data growth is to vast for Excel so I need to do this in Access and my Access knowledge is at best limited.

What I am thinking is if I can upload weekly the data set approx 130000 excel rows, then I can create a table that will pull in the dates I want to play with like this:
user enters a year ie 2013 and week number ie 20 and period to work with ie 10 weeks. Access then finds all the records that have 2013 in Year and weeks are between 10 and 20 inclusive.

OK, thinking last night this has a major flaw as if its 2013 Week 5 and you choose to work with 15 weeks you will end up with negative week numbers, so I need to rethink how I am going to achieve what Im setting out to do

But I'd still like to know how to do this, does anything make any sense yet

Back to the drawing board :banghead:

Martin

 

[JHB]

... OK, thinking last night this has a major flaw as if its 2013 Week 5 and you choose to work with 15 weeks you will end up with negative week numbers, so I need to rethink how I am going to achieve what Im setting out to do

I think you have to use a form and some VBA code to build the query string. Then you can decide one off two: If negative set it to week 1 or go into the previous year. But does the data coming from Excel have the year and week number in it, or does it have a date? Could you show some sample data, how it is stored in your Ms-Access table.

 

[CoffeeGuru]

I've stripped out a subset of the data this is the file I pull in from Excel


www DOT Box DOT com / s / uc84lynn3au5pkrmdvov
(remove spaces etc)


It does not contain a date, however its easy to add one before importing or maybe Access can add the date on import.
I use this excel formula to add the date from YYYY WW

=DATE(A2,1,1)+((B2-1)*7)-WEEKDAY(DATE(A2,1,1)+(B2-1)*7,2)+6
and copy down the whole file.
I have no idea how Access would do the same thing

Martin

 

[JHB]

I've made a solution for you in the attached database.
Open the only form, input year and weeknumbers, the result is shown in ResultTable.

 

[CoffeeGuru]

Hi JHB

Apologies I have not got back sooner.

I have looked at your db and, well it works. However I have no idea how and because of that I cannot apply it to my current build.

The fact it does work is amazing, so many thanks for the time you put in.

Would you mind stepping through the process with me, sorry if I appear dim.

If you are up for a challenge I can share my db with you, but its a bit of a mess, and largish.

I need to work out my forecast algorithm still, as I have a complex one working in excel that needs to be applied to Access and I think I need to draw it out as a set of instructions before I start keying anything in.


Martin

 

[JHB]

If you are up for a challenge I can share my db with you, but its a bit of a mess, and largish.

To reduce the size make a copy of the database, delete all unnecessary tables, forms, queries etc. and then Compact and Repair and at last zip it, and then post it here.
Then I'll take a look at it.