Database design/help

[WayneSteenkamp]

Hi,



I'm starting this over again so I can have all the answers I need in one thread and not multiple ones.

The database I'm doing at the moment is near completion, for what is needed of it anyway. The user has requested a few extra tweaks which I have been able to do, though there is a couple which I desperately need help on.

First of all to give you a slight insight of how the database needs to run:
There's 4 tables, ASE_Units, (ASE)Repair History, Sim_Serials and SimSerial_Repairtracker
The ASE_Units and the Repair_history are on a form together - Repair_history as a subform.
The Sim_Serials and SimSerial_Repairtracker are on a form together - SimSerial_Repairtracker as a subform

The User wants to record the ASE_Units and if theres a fault etc to it, the Repair_Tracker is filled out. The Same applies for the Sim_Serials and repairtracker. Except the Sims get cancelled on the repairtracker.


The Question is: The user wants to know if she fills out the SimSerialNumber in the ASE_Unit form - The data will populate not only the ASE_UNit table but the SIm_Serials table aswell? She doesnt fancy enter the Sim data twice - one in the Sim_Serial form and the other in the ASE_Unit form. She only asks this because some Units will come with a Simnumber. All other Simnumbers that are on their own with be entered in the Sim_sErial form.These Sims will have a status :"Unassigned"


Brings to the 2nd question.
At the moment any sims entered in the SIm_serial form will appear in the table as "unassigned: as default. How can i change the status automatically from "unassigned to "assigned" when a Unit is applied to it via the Ase_Unit form. The Status is currently in a lookup/combobox.

Hope this makes sense.
I've attached a print screen of my relationships, which I think may be incorrect. Especilly the Middle part when the SIm_serials and ASE_unit is linked. It doesn't seem to be doing anything. This because The SimNumber in the Sim_serial table is already in use?


Any help would be greatly appreciated.

[orange]

What is the purpose of this intended database? You haven 't mentioned anything that helps us put your request/comments into context.

[irish634]

If I understand correctly, you will want to change your relationship between the "simserials' table and the "aseunits" table to a one-to-many and check the "enforce referential integrity" and "cascade update" boxes.

Without seeing the database, I would probably make the field "SimSerialNumber" on the ASE_Units form a combo box. Then I would use the "Not In List" event to add the number to the Sim_Serials table when your customer typed in the number.

As far as changing your "status" use the after update event on your "Unit" field on the ASE form.

[WayneSteenkamp]

Sorry, The database is to record and log where the SimSerialNumbers and ASE Units are. i.e whether they are assigned together, which site are they installed, if they are faulty - what's wrong, what unit/sim is replacing which if damaged etc.

I am unable to change the relationship to a one to many or check the enforce referential integrity etc. It won't allow me. Tried this before. Not sure if its because the SimSerialNumber is being used already in the Sim_Serial table.

I will have to sit down and see if the combo box and "not in List" event would work or not. Need to see if i can get my head around it to see if that method would work. There will be over 2000 sims and Units being entered into the database. Will this make a difference or not?

The "status" im trying to change is only on the Sim Serial table. Not sure if its possible to use the after update event on both forms?

[orange]

For anyone interested, here is more info on SIM card.
http://www.wisegeek.com/what-is-a-sim-card.htm

[irish634]

The Serial in the SIM_Serial table should be a primary key (no duplicates) and the serial in the ASE table should be a foreign key (duplicates allowed?).

Check this out for using the 'not in list' : http://www.databasedev.co.uk/not_in_list.html

Yes you can set the after update on both forms.

[WayneSteenkamp]

Yes I have the Primary key and foreign key set like that. See attachment. Don't want duplicates no.
So with the use of the combo box I'd be able to have the SimSerial info entered in a form and the ASE unit and Sim tables will both be populated?

[WayneSteenkamp]

To change the Status by a macro..how do i go about doing it? what sort of coding do i need?