Possible in access? new to access

[snewuser]

I am completely new to access and databases in general and trying to decide if access is what I need for the task at hand.



I need to make a database that has a list of various models of a machine, then each machine will have a list of all of its parts but the parts slightly differ from machine to machine, then for each part there needs to be a list of all of the various dimensions of that part.

I thought access would be the best choice for this but now that I started looking into it it doesn't seem that way, I was looking for a way to have a list of all of the machines and then click on the machine I want and have all of the parts show up and then click on the part I want and have all of the dimensions show up.

Anything like this possible in access??

[dunc723]

It's definitely possible and Access is a good tool for it, but the usefulness depends on a good structure and good user interface. Your database is not that complex. Just one question comes to mind as I'm thinking about how I would structure it - will all the parts have the same set of dimensions with the same terminology? That is, will every part have width, thickness, length, weight, material... or will some have those parameters, while some will have diameter, length, thread, weight, material, and even others may have width1, width2, length1, length2, thickness... ?

[snewuser]

There will be about 15-20 parts per machine, every one of those parts is entirely different and will have a completely different set of names for each dimension because there very specific to that individual part. Even within that specific part name which may be in every machine, the dimension names will vary.

for example.....every machine has a shaft, the shafts are shaped slightly different and have different features which will have different dimensions. so one machine will have a shaft with say 25 dimensions and another machine will have a shaft with say 30 dimensions, possibly 24 of them the same names as the shaft with 25 dimensions.

[orange]

What other software/product were you considering? Why?
How has this been done in the past?

[snewuser]

What other software/product were you considering? Why?
How has this been done in the past?

This has not been done in the past it's to try and makes things easier instead of looking up dimensions on prints every time.

I was planning on just using excel and making mulitple files for every machine with multiple sheets for each part and then the dimensions on those sheets but someone suggested access to me.

I just have no idea how to use access and don't know how to go about this.

[orange]

Are you a single person organization, or multiple people/functions/departments???
Might be time to bring in somebody to review your business procedures and needs.
This seems a bit complex for a first database, and it probably has ties with various business processes.

[dunc723]

Here's the basic idea: You will probably have four tables with suggested field names:

Machines
MachineID (Autonumber, primary key) (It's generally good practice to have a unique record identifier automatically generated, rather than relying on text to be the unique identifier)
MachineName

MachineParts (I included this table because I assume you may have the same part in more than one machine)
MPID (Autonumber)
PartID (primary key)
MachineID (primary key)

This table is unusual, having two primary keys, but you want to do this to avoid assigning the same part to the same machine more than once.

Parts
PartID (Autonumber, primary key)
PartName

PartDetails
DetailID (Autonumber, primary key)
PartID
DetailName (a text description of the detail, such as Diameter)
DetailVal (the value you want to store, such as 32.1)

Then you will create relationships between the tables - this is where the magic happens. [Machines].[MachineID] relates to [MachineParts].[MachineID] in a one-to-many relationship - that is, there will be many records in MachineParts with the same MachineID relating to only one record in Machines with the same MachineID. Same for Parts to MachineParts, PartDetails to Parts, etc.

That does it for basic structure. You will probably want a Form for input, unless you have an efficient way to populate Tables. It's pretty hard for someone else to describe Form design without knowing how you want to enter data.

You will probably also want a Form for output, where, as you indicated, you click on a machine from a list, which populates another list of parts for that machine; click on a part and it populates another list with parameters. That's not too complicated, but it requires some VBA programming behind the form to make it behave the way you want. And all this does is display the information you specify - it doesn't give you any printed output. That requires designing a report, and you can programmatically send the information you are displaying to the report.

There's an awful lot of detail that has to go into the design of all these components to make it functional and fairly robust, and there's just not enough room here. You need to review tutorials and samples and the (not very good) Help documentation, then post questions here to help with small problems.

Side note: As an engineer, I really hope you are still forcing the drawings to be governing documents, and using this system for information only!

[snewuser]

dunc723 that was very helpful thank you very much. I had trouble understanding it all because again I'm brand new to databases but the more I mess around with it hopefully I can figure it out.

And of course this is only for information only, just a place for quick reference for doing calculations.

[Dal Jeanis]

snew - If you're in an ISO 900X organization, make sure you review your processes. It would be an expensive OOOOPS to have someone relying on your database as a convenient source and then find out that a later drawing existed where the dimensions were changed.

[snewuser]

snew - If you're in an ISO 900X organization, make sure you review your processes. It would be an expensive OOOOPS to have someone relying on your database as a convenient source and then find out that a later drawing existed where the dimensions were changed.

The reasons this is going to be used everything should be fine and its a hope that eventually it will be someone's responsibility to update this and keep it accurate.