Some questions from a noob

[Seamus]

Hi guys

I've been asked to design a stock control and tracking database at work, but have fairly limited knowledge of access and am struggling. Hopefully some of you guys will be able to help me out

First, some background information. We are making emergency lighting kits for differing vehicles. Each vehicle has a number of sub assemblies, which in turn has a number of components. These components will come in batches and the same components can be used in different assemblies, even in different vehicles (things like nuts, bolts, screws etc). Each final kit will have an individual box number.

The aim of the database is to be able to call up a particular component, find out what batch it came from, and determine what assemblies and kits it has been used in, for traceability purposes.

Where I am struggling is finding a way to do this, without the need for loads and loads of tables. I imagine there's some SQL programming needed here, which is completely foreign to me, so any advice is welcomed.

I've attached a zip file of how far I've got with the database so far, which should hopefully give a better understanding of what I'm trying to achieve.

Thanks in advance

[Seamus]

Ok, I've managed to work out the design of the database on my own, but now there's a couple more problems.

Firstly, I have a form where I need four combo boxes to cascade down- the idea you select a BoxNo from the first, an assembly from the selected box in the second, a PartNo from the selected assembly in the third, and finally a BatchNo from the selected part in the fourth. I think the problems I'm having stem from the fact the relevant bits of data are all in separate tables. I've managed to achieve a simple test database, containing two cascading combo boxes, using countries and cities as examples, but I'm struggling to apply the theory for that to the database proper. Any help much appreciated.

Secondly, I need to write a query that will filter out all parts with the same batch number and display where they have been used. This is easy enough in a table, but I'd like there to be a command button that runs the query from a form.

I've attached the updated database, to give you a better idea of what I want to achieve

Thanks in advance for any help

[maximus]

can you upload the sample in access 2000 format.

[Seamus]

No problem, hopefully that should work for you.

Cheers