Normalization with no duplicates

[tjcinnamon]

I have a PO DB that I'm trying to normalize. For the most part it is normalized but there are 2 fields that are not normalized because they repeat data. So I'm trying to restructure a bit and having some problems when it comes to data entry via forms.

There is also a PO table above this one but is not used in the subform for the details.
My old set up for my PODetailsTable

PODetailsID pk
PO fk
Item
Quantity
Price
Allocation

I want my new set up to be
PODetailsTable

PODetailsID pk
PO
ItemID fk
Quantity
Price
AllocationID fk

ItemTable
ItemID pk
Item

AllocationTable
AllocationID pk
Allocation

On my form I can't enter anything that is already on the ItemTable. I looked at some microsoft sample DB's and they have you enter the item from a different form then you can select it in from the combo box which would really slow down data entry peticularly early on.

Is there a way I can type the Item in and if its new, it will be put it on the ItemTable associate an ItemID to both tables?? But if its already in the Itemtable just apply the already existing PK on ItemTable to the FK ItemID on the PODetails Table for that record??

Here is a A2K format of it which is how it currently works. In the relationships window you can see an example of how I want it but can't get to work.

View attachment PODB.zip

Thanks JOe K.

 

[The_Doc_Man]

The typical way to do this is to enter your data to an unbound form and do a DLookup behind the scenes to verify uniqueness. If not unique, out with a message box and off with their heads! If unique, open a recordset to the real table, .addnew on the recordset, populate the fields, and .update the recordset. Or just bind it to a field that is indexed, nodups, and let Access kvetch about it for you.

But if you trap it yourself and roll your own error routine, you have the chance to make it prettier than Bill Gates ever would have.

 

[tjcinnamon]

Thanks for the reply. I wasn't sure if a properly normalized database would require code behind the scenes to keep it normalized. I actually got it to work after posting this but its good to know im on the right path.

 

[The_Doc_Man]

tj, it is not that you need code to keep data norrmalized once you have set it up. It is that getting it that way can be a bit ugly to begin with. Once the indexes are set up (i.e. once Access ACCEPTS your indexes), keeping things properly normalized is a lot easier. My answer was based on reading that you were converting something and still had manipulation to do.

Particularly when retrofitting a new DB to existing data, there are rough moments when you SWEAR that you will never get it normalized or ready for referential integrity or any of the other features of a good DB. The veterans on this forum have been there and done that, so we feel for you. But we also can testify that it HAS been done by others, that it is eminently doable with patience and persistence. Plus some skull sweat.