Populate four tables from one entry

[Angel1974]

I have a database which relies heavily on a scanned item number (non repeating and NOT a primary key) which needs to be entered into 4 tables, to save time I was wondering if it can be entered into the main table and autopopulated into the remaining three tables?

I am very new to access so please be gentle with your explaination

Thank you in advance,

Angel

 

[midmented]

Research cascading update and delete under relationships. You'll need to assign a primary key for each of the 4 tables to accomplish it though.

 

[Atomic Shrimp]

May I (gently) ask: why do you need to store the same value in four different places? Why not store it in the main table, then look it up, using the key fields to link to the main table.

 

[Pat Hartman]

midmented,
Cascade update will only cascade primary key values to related foreign keys. It will NOT cascade non-PK values.
Angel,
As Mike implied, it sounds like you need help with your table structure and we'll be happy to help.
Welcome aboard.

 

[Angel1974]

Well to be honest I have no idea why I need it to work like this, it just sounded the easiest way. However, if you know an easy way by all means we'll go that route.

I've posted the database so you can see where I'm at and you will have some idea of what I'm talking about. There are now 5 tables to use the value.

The way I currently understand it is that to use the value in the other tables it cannot be a lookup value as the value doesn't really exist except on another table??

What I want is each table (Appraisal, Finance, Postal, Buyer & Seller) to be populated by the item number when entered into the item table (main table) so each table has an entry linked to the main item and an open entry in the table (earmarked) as each entry will be filled in at some point within a few weeks.

Now, from my point of view (as a collectable dealer) I can only see one way forward, but as you guys are much more knowledgeable than me I'll let myself be guided by you and your infinite wisdom

Thanks

The databse is access 2007 renamed to .mdb so I can attach it.It also isn't anywhere near finished but is roughly all there in the right sections. The native file extension for the db is .accdb (?!?) which I have been told is difficult to work with but I don't know how to change it.

 

[Pat Hartman]

If you actually save the database as an .mdb from within Access 2007, more people will be able to look at it.

 

[Angel1974]

Hi Pat, I thought this myself but I don't seem to have that option when I choose 'save as', all I have is .accdb and when I try to save in earlier versions it tells me I can't because of the options I am using in my tables.

If somebody cam tell me how to save the database in .mdb I will do it immediately and upload it.

 

[Pat Hartman]

If you have used A2007, you can't save it as an .mdb. Post a picture of the relationship window or use the print option and save the report as a .snp which you can attach here. Try to expand the tables so that all the columns are visible and the layout makes sense.

 

[Angel1974]

Good idea, here is the scrren cap of the relationship tables (others removed as they aren't used in this scenario).

I would like to be able to add the item number into the items table and have it automatically add the same value to the tables for finance postal and appraisal.

There are no relationships for postal and finance right now because I was trying to get one table working first - probably the wrong way though! Also postal has a few more fields needed, including the item #

Any help is very much appreciated, this is a BIG learning curve.

Many thanks

 

[Pat Hartman]

You need to do some studying on normalization. Your tables are not constructed correctly. The primary key of the one-side table is stored as a foreign key in the many-side table and that is how the relationship is made. NO other field from the one-side table is ever stored in the many-side table. You have Item# which is a data field in the Item table copied into several other tables, it needs to be removed. You also have what looks like crossed references so I can't determine which table is the one side. For example, you have Item ID in the appraisal table and Appraisal ID in the Item table.

BTW - column and table names should not contain embedded spaces or special characters. they will cause problems with VBA.

 

[Angel1974]

Thanks Pat, I thought I had constructed them correcty based on a lydia tutorial but I obviously mis-understood what they were trying to teach, it was a bit technical for a beginner.

So if I understand this correctly, the relational database is one HUGE table (not physically of course but in theory), which is split into more manageable parts. If all of the parts were to be put together to make the theoretical table, there would be no duplicate column labels, and each (section) table would be linked by a unique ID (primary) to a foreign key in it's linked (section) table, i.e. Item ID (primary) links to Item ID (foreign).

Also, that each link must be unique, not using one link for two sections?

So I can use Buyer ID in it's own table as primary, and in items as foreign but not in anything else.

I will have to re-do the database anyway, so the question is this; if I re-do it in access 2003 can I save it as .mdb and can I still use embedded pictures the same way as in 2007?

Phew, too much to think about this early in the morning.

Once again thanks for your continued help on this matter, it really is frustrating to have a basic understanding but little else.

 

[Pat Hartman]

Let's start with the embedded pictures. Versions of Access prior to A2007 bloat horribly when graphic objects are stored so you really don't want to have to store too many of them.

The entire database isn't one flat "table" because not all parts would ever be joined in the same query. One hierarchial path could be represented as one flattened table as you describe. For example - Customer-->Order-->OrderItems but if you also have a customerContacts table that may hold multiple contacts per customer, you could not include it in the join above, because doing so would duplicate orders. You would use a separate query to work with the contacts - Customer-->Contacts.

 

[Angel1974]

Ah that could be a problem as each item would have a minimum of three images! Although I could code the database in regular access (97 is it?) for the actual database so I can share it here and get the main thing done and then I assume I can convert it to 2007 in access?? If so I can add the image part then as it isn't linked to anything other than the one table.

I think I understand how it works now, so basically it is to avoid negative space in the database when the records are recalled. Using each 'section' as a mini database to store the information so it can be collated when needed.

Is that close?

lol

 

[Pat Hartman]

Yes a properly normalize schema will avoid negative space as you call it. In relational terms having duplicate data leads to what are called update anomolies. That is when you update one instance but not others so what started out as the same value becomes different over time. When you define the relationships in the relationship window, be sure to select the enforce RI checkbox. That will ensure that you don't add a record in a "child" table with a foreign key value that doesn't match one of the existing rows in the "parent" table. RI also prevents you from deleting parents that have children unless you have also selected cascade delete. Not all hierarchial relationships are suitable for cascade delete. For example, the Customer-->Order-->OrderDetails relationship. You do not want to specifiy cascade delete on the Customer-->Order relationship because you generally don't want to delete customers with orders. However, you do want to specify cascade delete on the Order-->OrderDetails relationship because when you delete an order, you want its dependent details to also be deleted.

 

[Angel1974]

Just a quick thought, I've started laying out the tables in Access 2000 and I have added the tables to the relational window.

Question; can I use the primary key for the maintable to link three other tables for the foreign key? See attached image.

 

[Pat Hartman]

Yes you can have multiple tables with 1-m relationships to the 1-side table. You haven't put any other data fields in the schema so I don't really know what those other tables will be used for so I can't comment on whether or not they make sense.

 

[Angel1974]

Ok, I have added the fields to the tables that are being used here. The Data table is just for lookups and has no repeating values, and the Saundries and Supplier tables are not linked in any way to the other tables and also have no repeating values.

There is one thing however, in the relationship window as you will see the tables to the right are all 1 to many but there cannot be more than one record for each item in each table, how can I change this to 1/1?

Anyway, here is the new screenshot of the relationship window, does this look any better than before and can you see any obvious errors I have missed?

Thanks

 

[Pat Hartman]

I think the relationships to financial, appraisal, and postal are backwards. You should have those IDs in the items table rather than having ItemID in those tables.

 

[Angel1974]

Ah yes, that solved it. If I have the other ID's in the maintable though, does that mean I will need to manually add the relative ID to link the tables?

Anyway, database attached if somebody would kindly look over it to see how I'm doing.

Thanks.

 

[Pat Hartman]

You ALWAYS need to populate the foreign key manually. How else would Access know what records are related? The only deviation from this is when you use a mainform/subform. If the master/child links are correctly set, the foreign key of the subform is automatically populated.