Updateable form Displaying data for different weeks of the year from same field

[flozza27]

Hi all,
I've spent a lot of time trying to work out how to do this without success. I have scoured the forums and havnt been able to find an obvious similar situation, which leads me to believe I probably shouldn't be doing it this way - but it kind of just has to be like this.



I have a table called tbldata that contains forecast quantities and dates required of particular supplies (as well as foreign key from supply table). For ease of data entry and oversight I'm trying to create a form where user can enter supplies required for each week. I want the form datasheet to display something like this:

Supplies(tblSuppliesFK) Week1 (TblData.Quantity) week2 (TblData.quantity) Week3 (TblData.Quantity) Week4 (TblData.quantity) bread 20 1 3 5 carrots 10 40 0 10 meat 10 0 20 3
Weeknumber is stored in a WeekNumber field of tblData, and above results for each column would need to be filtered by =(DatePart("ww",Date())), =(DatePart("ww",Date()))+1, =(DatePart("ww",Date()))+2 etc. I have created an append query that inserts a value for each weeknumber for each supply as a 0 quantity on commencment of a new week. my problem is that as soon as i add more than one week im no longer able to change the data, if i create multiple subforms i can no longer be sure that the rows contained in subsequent weeks will line up with supplies and could result in incorrect data being captured. I would be most grateful if someone could provide a suggestion on how to do this (or closest possible alternative) thanks

[orange]

I recommend that you work through 1 or 2 of the tutorials from RogersAccessLibrary. You will be led through a process to identify the things important to your "business" and their attributes. Each tutorial will take 45 about minutes to 1 hour, but you will learn about database design. And what you learn can be used with any database.
From experience you have to get the design identified and tested before jumping too quickly into physical database. The tutorials will lead you to a blue print of a database to support your business.

Tutorials from RogersAccessLibrary:
ZYX Laboratories

Class info Systems

Catering Business

See the Database Planning and Design link in my signature for more related info.

See this link for someone with a similar situation as yours and the dialog.


Good luck with your project.

[ranman256]

first , if that is your table design ,then it is wrong. Your data should be :
supply, Week#, Qty
bread, week1, 20
bread, week2, 1
etc

then the table can be queried, etc.
the end result can even look like your example.

[flozza27]

Hi Ranman256,
Thanks for taking the time to respond.
just to clarify, what you are saying is my mistake is that im using a foreign key from the supply table rather than just text in my data table? otherwise the table design is the same as your example. What I'm trying to achieve is for it to be a four week ahead planning tool (so effectively next week week 2 data would move into week 1 and become irrelevant the following week)

[flozza27]

it might make more sense if I post the relationships.