Question Creating a Subsea Stock Database

[SandyB]

Hi I as you mey see from my limited posts I am new to Access, I can create simple databases ok,but now want to get a bit adventurous and create a spares stock list, easy enough, but the problem is that in the DB I will have items with the same part no, but different serial numbers, but I will also have a Qty column. A sample record is below.

P/N - 123456
DES - Valve
S/N - 987, 654 and 321
QTY - 3

Problem I have is I do not know how to create a record with multiple serial no's that tie in with a single part and but also have multiple Qty's if that makes sense.

any help would be appreciated.

Thanks
SandyB

 

[Mihail]

You must create 3 tables:
tblParts
ID_Part - AutoNumber (PK)
PartNo - Text

tblItems
ID_Item - AutoNumber (PK)
SN - Text

tblPartsContains
ID_PartContain - AutoNumber (PK)
ID_Part - Number (FK)
ID_Item - Number (FK)
QTY - Number

 

[MarkK]

Don't put multiple serial numbers in a single row. Every discrete thing you want to keep track of should have its own row.

 

[SandyB]

You must create 3 tables:
tblParts
ID_Part - AutoNumber (PK)
PartNo - Text

tblItems
ID_Item - AutoNumber (PK)
SN - Text

tblPartsContains
ID_PartContain - AutoNumber (PK)
ID_Part - Number (FK)
ID_Item - Number (FK)
QTY - Number

Hi Mihail, thank you for this, I take it that PK = Primary Key and FK is Field Key, if this is the, I am assuming that the final table in my case should be something like this proving i make the correct additions to the to the table you have shown me.

ID_PartContain - AutoNumber (PK)
ID_Part - Number (FK) - Own Company P/N
CST_Part - Number (FK) - Add New to TBL for customer assigned
ID_Item - Number (FK) - Serial No
QTY - Number

Loc - This is a location that can be the same for multiple parts
Short Desc - ie valve
Long Desc - Full Description of part ie valve size and diementions
Com - This is a comments field
Last Checked - a date field to show when the item was last check

Thanks
Sandy

 

[jdraw]

Try to avoid jargon-ese --- use full words, no spaces, no special characters in your field and object names.

I think it would be helpful for readers if you could describe your business in simple terms to give us an overview of what you do, what is involved, the relationships of the things involved.

Here's a sample of what I'm asking for:

Narrative
ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

from RogersAccessLibrary

 

[Mihail]

FK = Foreign Key

 

[SandyB]

Thanks to every one so far for the comments, and suggestions to help readers understand what I am looking for so here goes.

I currently work for an oil and gas company looking after a specific customer, the customer has spares which are held at our stores facility, currently the spares listing is on a spreadsheet, whilst this is ok the customer now want to have picture of the spares and not to want a very large spreadsheet i felt that Access could be the best solution.

the database would need to be able to hold information and pictures, something i know it can do.

These are the fields that i cam looking to do are.

Uniquie ID (Primary Key, Auto Number), My Company Part No, Customers Part No, Serial No (for each part there is a uniquie Serial No, but the part No can be the same), Quantity (there can be multiple quantities of each part), Locataion (multiple different parts may be in the same location), Item Type, Long Description, Comments and finally Date last checked.

Thanks, I hope that this makes what I am trying to do a bit clearer.

SandyB

 

[Mihail]

Is better but can be improved.

How the stock is checked ? By location or by overall ? I speak about a certain type of spares.
Have you ever need to group spares by parts ?

 

[SandyB]

Hi Mihail,

Thanks for the response, normally the stock is checked by location, however there may be times when soething has moved location for whatever reason, ie when it has been out of stores for use then it may not go back to the same location, this is something that we are trying to rectify and hopefully this DB will allow me to do that. My intention is that when a part has been returned then it should go back to the location that it came from.

Thanks
Sandy

 

[Mihail]

For me is equal where walk yours spares. But Access do not accept gray answers. White or Black is all he understand.
If you will look carefully to my previous post there are TWO questions in. And you don't answer to any.

 

[SandyB]

Hi Mihail,

Apologies - Check is an annual visual count Plus periodic maintenance visual checkes, Normally as I mentioned they are checked by location, ie LOC 55RL. For search purposes it would be handy to do group parts so that a search by part type can be carried out, ie. Valves, Accumulators etc.

Thanks
Sandy