Return a value on to my form depending on the date/week number

[MrXYZ]

Hi,

I have a form and I want it to show me stock levels for a particular week.

My table lists each item number and then tells me the stock that we have for each week by number.



On my form I want to return the quantity of the item that I am looking up, for each week. So I have a field on my form that I want to show week 38 and we have 20 in stock. Then in another field, to show me week 39 and we have 50 in stock.

I have gotten this far, however, I want to update the table each week with new data, that does not include last week, so when I import the table, I want the form to update with the current weeks information, and the current weeks plus 1, so on and so forth.

I hope this makes sense?

Any help greatly appreciated

[June7]

What is the table structure? You have a field for each week - 52 fields?

[MrXYZ]

Yes, sorry, this is exactly my structure. Each field is labelled by week number.

Thanks

[Missinglinq]

Yes, sorry, this is exactly my structure. Each field is labelled by week number.

Then you don't have a Relational Database, you have a Spreadsheet, which is what June7 was getting at!

Check out CyberCow’s list of links (in Post #5) on the subject of Normalization:

http://www.utteraccess.com/forum/Use...-t1986675.html

Once your Database is properly Normalized, this and most other tasks will be much easier to do!

Linq ;0)>

[MrXYZ]

I have relationships within my database. All my tables are linked by the item number. They all feed into the master stock listing.


Will check out your link though - thanks.

[June7]

Linq is correct that your structure is not fully normalized.

It is a balancing act between normalization and ease of data entry/output. I have a project that doesn't fully fit 'normalization' but it suits the way we want to output data.

However, from the description of how you want form to work, would seem you need to normalize your data a bit more.