I'll start by saying Access is fairly new ground for me, so please be gentle.

I'm trying to build a database to keep tabs on mobile phones (numbers, SIM Numbers, IMEI Number, model and users name), together with the date each change happens, and a comments field. I need to be able to record the history of each phone number, so that if the SIM/IMEI or User changes the old and new data is retained.

Not sure I am going about this the right way. So far I have made these tables:

Mobiles
ID
Mobile_No (unique index)

Comments
ID
Mobile_No
Comment
Date

SIM
ID
Mobile_No
SIM
PIN_No
Date

IMEI
ID
Mobile_No
IMEI
Date

and created one-to-many relationships between the mobiles table and each of the others using the mobile number as the key.

From there I have knocked up a form to bring up a mobile number and display (in subforms) the records from each of the other tables that relate to that number. All of this works as I expected it to.

This is where I get out of my depth!

From this form I would like to be able to allocate a new SIM/IMEI/User to a mobile using drop down lists from the respective table and add a new SIM/IMEI/User details to the respective table.

I also need a way of ensuring that the same IMEI number is not recorded as the active record against two mobile numbers at the same time.

Is this possible or am I asking too much?

 

Anyone?

 

Fig: YHPM

 

So do you