what data type do I use that will allow me to enter unique values and select from predefine

[Fredthetech]

Hi everybody,




I'm new to the forum and new to Microsoft Access and database creation altogether. I'm attempting to build out a simple inventory database of assets that belong to my department. I've taken a few entry-level courses but still seem to struggle to understand how specific components and functions.
I was hoping somebody could help me figure out an issue I'm currently having. It's most likely a straightforward issue that I don't know how to resolve.


The issue: I have to list all of the PCs by system name in my database. But some of my equipment does not have PC's which will require an entry that says " Stand Alone (No PC)." my question is, how do I normalize that? How do I have the " Stand Alone (No PC)" option available while still entering unique values if needed? I don't want redundant data..


Example of PC name table.


PC name
FRTHGWHEC745
FRTHDWBQC746
Stand Alone (No PC)
Stand Alone (No PC)
Stand Alone (No PC)
Stand Alone (No PC)
FRTHBQQVEC753
FRTHGINVBEC755
FRTH10T7J09RK52


I apologize for all the noob questions; I appreciate the help in advance.


Thank you very much,

[CJ_London]

you don't - what you do do is have a separate autonumber field to uniquely identify a record.

I do get the feeling that your data is not normalised.

There are workarounds - leave the value as null if there in no PC (you can still display 'Stand Alone (No PC)' when required using the format property, the nz function or the the isnull function depending on the situation - but not enough info to see that would work

[mike60smart]

Hi Fred

Can you upload a zipped copy of your database so we can see your current table structure?

[Micron]

What I don't get is how that table can be helpful if that's all the fields in it. Even a unique record id won't tell you the difference between records 3 to 6. IF they were unique values then such a table could be used to lookup those values, but such a table would not have repeating records.
I'd encourage you to upload as well because I suspect you'll be making all the classic beginner mistakes. What you posted for a table name suggests that.

[ArviLaanemets]

For start, you have to make clear, is your database about some equipment, where some of them may have PC(s) attached, and some not, but the main object is equipment, and all you need is the name of attched PS(s) in your network. Or it is about all PCs and you have there only a couple field(s) with some info about equipment, they are attached to? Or is it something entirely different? The answer may affect the optimal db structure a lot!

[Fredthetech]

Correct, my issue is I'm trying to normalize the database. I have all of the criteria that needs to go into the database. A lot of it is redundant criteria that I do not want to enter into the database repeatedly. So I'm looking for a way to have options available when populating data. I want to enter the PC's name, but if the PC does not have a name available, have it display 'Stand Alone (No PC).'
I will read up on using the null function and see if that may help. I appreciate your feedback. Thank you.

you don't - what you do do is have a separate autonumber field to uniquely identify a record.

I do get the feeling that your data is not normalised.

There are workarounds - leave the value as null if there in no PC (you can still display 'Stand Alone (No PC)' when required using the format property, the nz function or the the isnull function depending on the situation - but not enough info to see that would work

[Fredthetech]

My company has a proxy in place that will not allow me actually to upload anything anywhere from the company system. Which I'm on right now. I will see if I can find a way to get it zipped up and posted here. There will not be any proprietary information in it. Just the database with the tables I'm interested in creating relationships between.

Again, please bear with me as this is my first attempt at making a functional database. The functions I believe I'm trying to implement are more advanced than any of the intro database courses I have taken in the past.

Hi Fred

Can you upload a zipped copy of your database so we can see your current table structure?

[Micron]

If not against policy, copy to a thumb drive, take it home and do it from there? Or mock something up that has nothing to do with the business but from which you'll be able to apply the same design. Could be about fruit, or anything else. Then take that home.

[Fredthetech]

Very good question,

This database is going to be pretty in-depth. My objective is to create a system where my team no longer needs to use an excel spreadsheet to manage inventory data. This spreadsheet has become very big recently, and I think it would be more appropriate as a database. I'm hoping that creating a database will also give us options for using GUI interfaces, Or other tools like Microsoft Teams, power app, and power automate.

Different annual maintenance actions need to be taken for each device. Not every device or piece of equipment will have a PC attached, so fields pertaining to PC information such as make, model, serial number, asset tag, operating system, Or some PM actions may not apply.

This inventory list contains a lot of different criteria, including The following items found below. I've tried to break them up into logical groupings. I do not believe it would make sense to create one table for all of this data. But instead, break these data sets up into multiple tables. And have them all relate to each other. (One too many, and many to many)
The result I'm looking for is to select a device by its unique device name (known to this production facility) and pull up a list of the user contact information or the annual periodic reviews that need to be conducted for each system or the PC information. I do not want all of the data every time I open the database. Just the pertinent information I'm looking for related to each unique ID.

Unique ID
Location
System Owner
Contact
Group owner
--------------------
Networked
IP address
PC Name
Asset Tag
S/N - Chassis
PC support provider
Model
Operating System
Operational status
Device Type
Equipment Model
Software
Data backup option
---------------------
Passport #
Admin Log USER
Admin Log Deskman
Admin SOP
Oeration & Maintenance SOP
Access Forms
-----------------------------
Annual System User Account Review
Annual System Certification
Monthly Audit logs Archive
Monthly Audit logs Purge
Monthly Data Purge
Annual Data Purge
Network Drives and Folder Access Review
Weekly Data BackUp
Monthly Data BackUp
Quarterly Data BackUp & Integrity Verification
Semi-Annual Data BackUp & Integrity Verification
Annual Data BackUp & Integrity Verification
Annual-System Administration Audit Trail review
Periodic Rebooting and Hard Drive Defragmentation
Monthly Date and Time Check
Annual Date and Time Check
Semi-Annual Day Light Saving Time Check

[pbaldy]

Post 9 was moderated, I'm posting to trigger email notifications. I'll delete the duplicate moderated posts.

[CJ_London]

just taking the first section and with limited information

Unique ID
Location - should be in a separate table of locations with a PK, this table then has an FK
System Owner - should be in a separate table of employees? users? with a PK, this table then has an FK
Contact - should be in a separate table of employees? users? with a PK, this table then has an FK
Group owner - should be in a separate table of employees? users? with a PK, this table then has an FK. Or it may be the system owner reports to group owner, so no need to have the FK here