Access Tables Create

[kris9]

Hi,



I need to create Access tables for migration.

One user can have multiple laptops.

Is this the best way to create the tables as shown below. (PK – primary key & FK – foreign key)

Table 1: User ID(PK), User name, Email, Contact, Comments

Table 2: Laptop ID (PK), User ID (FK), Location, Desk, Make, Migration Date, Migration Status


Can I create a query to join these two tables together so I can use the table view to enter the details ?

Please assist

[recyan]

Just a few thoughts (do not know what migration means):

tblUsers
User ID(PK),
User name,
Email,
Contact,
Comments

tblLaptops
Laptop ID (PK),
Make,
Description,
........

tblUsersLaptops
UsersLaptopsID (PK)
User ID (FK),
Laptop ID (FK),
Migration Date,
Migration Status
Location,
Desk,

Thanks

[kris9]

thanks for the reply. I created tables as suggested and created the query for it.

when i enter the record in query, i am getting "access database engine cannot find a record in the table with key matching field"

[recyan]

My suggestion was based on my perception.
Please take in to consideration the functional scenario at your end during design phase.

Would suggest posting the SQL of your query which is throwing the error. That would enable some one to guide you along.

Thanks

[kris9]

am unable to update anything in query now.

SELECT MigDB.[User ID], MigDB.[Workstation ID], MigDB.[Migration Date], MigDB.[Migration Status], MigDB.Location, MigDB.[Desk], Employee.[Employee Name], Employee.[Email ID], Employee.Contact
FROM (MigDB INNER JOIN Employee ON MigDB.[User ID] = Employee.[LAN ID]) INNER JOIN Workstation ON MigDB.[Workstation ID] = Workstation.[Workstation ID];

[recyan]

Is the query not returning any records or Do you have some other problem ?
If the query is not returning any records, run the query part by part & see if any records are returned. Also, manually, check, which record/s, you expect the query to return, based on the joins you have used.

Thanks

[kris9]

created the form (datsheet) but unable to add data to some of the fields. might be some relationship/index issue. not sure what am doing wrong.

Attached my DB.

[recyan]

Am not much in to Forms. Hope some body comes along & takes a look.
At first glance, it appears that you do not have any records in the Master tables for Employee & WorkStation, which is perhaps the reason for the Form not allowing any entry in to the MigDB.
Try adding a couple of dummy records in the Master tables for Employee & WorkStation & then see how it goes.
Also, in case you have not got in to Forms & SubForms, Just take a look.

Thanks

[kris9]

can someone please assist.. attached my sample db in my previous post