Newbie - help with really basic design question

[sailorguy]

Hello,

I have a situation where I had data in Excel which is hard to manage and I THINK is the perfect situation to move to a database but I'm stuck.

I have a list of People, Assets1, Assets2, Assets3. Each person can have only 1 of each type of Asset, and each of the Assets can only be assigned to 1 person.

I built 4 tables like this:

tblUsers

UserId (PK auto number)
Last Name
First Name
Asset1
Asset2
Asset3



tblAsset1

Asset1 (PK unique values)
UserId


tblAsset2

Asset2 (PK unique values)
UserID


tblAsset3

Asset3 (PK unique values)
UserID



I'm not sure of the proper relationship for these tables. My main goal is a "main" form where for each User, you select an AVAILABLE Asset1, Asset2, Asset3.

This seems like the most simple form of an Access DB but i'm having trouble. Can someone get me started here????

 

[Kryst51]

To get you started, and I really mean only a very basic start, Your tables (given the info in the first post should be like this:

tblUsers
UserID- Autonumber, PK
FirstName
LastName

tblUserAssets - Junction table between Users and Assets
UserID- Number, FK
AssetID- Number, FK


tblAssets
AssetID- Autonumber, PK
AssetName
TypeID- Number, FK

tblAssetTypes
TypeID- Autonumber, PK
Type

You'll form the relationships in the relationship window. DO NOT use Lookups at the table level DO use Lookups on forms/reports (for why click on the link in my signature that talks about it). You will also use data validation at the form level to ensure that each user has only one of each type of asset.

Edit: Oh yeah, dont use space or special characters in your table names or field names as this will make things harder later. Also, get your table structure correct before attempting anything else, it is the most important part of the database. My answer only takes into consideration what you have told in your Original Post, it may not match exactly what you are doing. I suggest reading up on normalization, as that will help you to get your table structure right. Search on this site or on google, there is a ton of stuff out on the internet about it.

 

[milehighfreak]

This problem sounds a lot like what I'm trying to figure out at the moment, except without all the restrictions. I need to import multiple listed values from an external source into a single list field. I think I'll keep an eye on this thread; I'd help you but I have to go. I'll be looking later though.

Edit - Kryst, there's a way to enforce the uniqueness of asset IDs both systematically and within relationship contexts. If you can't do it without a form, you can't do it.

 

[milehighfreak]

Had an inspiration, whipped this up. Tell me if it's useful. Still not great for me since I've gotta make a format where I can efficiently input dozens of sub-data sheets like that.

 

[sailorguy]

Thanks folks!

I'm going to look at this this weekend and see if I can get anywhere. I tried looking at this from multiple angles but never seem to get far. I just keep thinking...this is SO simple, (unless you don't know what you're doing).

 

[milehighfreak]

Thanks folks!

I'm going to look at this this weekend and see if I can get anywhere. I tried looking at this from multiple angles but never seem to get far. I just keep thinking...this is SO simple, (unless you don't know what you're doing).

Well did you look at the example db I gave you? It should have the exact structure you're looking for.

 

[sailorguy]

I "think" that helped, but I guess I'm now at the next step. I want my main form (frmUsersMaster) to allow me to enter "available" TN, DN, DID for each user using a combo or list box.

That's where I'm stuck now but I guess it belongs in another forum group!

It seems to show the records correctly in the form if I manually enter the UserID into each of the other tables.

Here is my DB which is really very much the same.