always using too many fields

[fordy]

Whenever I use access I always find myself, putting together hundreds of wueries or fields, does anyone know why this is. the latest a stock control database, where a shop has 25 different types of stock, means I have built a form to allow for the 200 fields to go in it.

e.g

stock in 1
stock sold 1

stock in 2
stock sold 2

is there any way around this?

 

[simongallop]

Have a list box with the stock then double click on the item that you want to view and get the details, such as in, sold, price, profit etc to show up in textboxes on the form

 

[David R]

Read an article on database normalization and database design. 25 items should mean 25 entries in a reference table, NOT 25/50/75/175 fields in your main table.

If you're not familiar with one:many relationships, become so. Please.

HTH,
David R

 

[fordy]

So do I need 25 tables, and create a relationship, from main to the other 25.
I cant see this as being right.

e.g

main, stock id to table 1, stock id.

I just keep having to copy and paste tables and queries all the time, and renaming them, differently.

 

[Rich]

As recommended previously look at Northwind and Orders sample db's supplied by Microsoft, you will save yourself a great deal of work and trouble.

 

[fordy]

Thanx very much, it helped a great deal

 

[fordy]

Ive been wasting so much time, now I only have the five different fields, and have created two one to many relationship to porduct id and order id.

Thanks for your help.

However Im still struggling to carry forward data. Is it a pain for the user to look at previous stock take and transfer figures across

 

[David R]

Glad you got it working and better normalized. Isn't it a relief when you can delete all those repetitive fields?

Can you explain a bit more what you're trying to do now? With queries you can 'reconstruct' most of what your weird table structure looked like before if you need to while you rework your forms/reports.

What does it mean to "look at previous stock take and transfer figures across"?

David R

 

[fordy]

Hi David, Thanks for your reply. I have to produce a database for a jewellery shop. Stock in, stock sold and stock carried forward on to the next month.

The only thing I havent worked is how to carry the [stock remaining] figure forward into the [stock in] field of the next month. Do I use dlookup, move to, or the carry forward code publish in one of the microsoft knowledge based articles.

Thanks for your all your help


Fordy

 

[David R]

Try searching the archives for 'inventory database exampl' and see what comes up. I got about 7 hits for that, if you vary the wording you can get other responses. I've never done an inventory database myself so I'm not sure the best way to go about it.

Good luck,
David R