relationships

[smiller]

I have one table that contains all the base data for parts that I have. Numerous fields identify a part. No one category can 100% define it. I have another chart that I want to link to each part in the main table. I am going to be inserting values as parts are inserted or removed for use. In the resources that I was looking at, it said I was supposed to match the primary key in the main table to the matching field in the other. For both tables, though, the primary keys are just auto numbers that really have no significance. Any help would be greatly appreciated. Eventually I would like to set up the two tables so when I input values into the minor chart as parts are taken or returned, it automatically updates values in the primary table. Is there any specific matching I should be doing now for setting that up? Thanks!

 

[ajetrumpet]

Eventually I would like to set up the two tables so when I input values into the minor chart as parts are taken or returned, it automatically updates values in the primary table.

Might want to look at this for a reference, Raffers I think was asking the same thing. Kind of sounds like you are storing the same data in some of the fields in two different tables. If you're wanting to update certain values as they change, an Update Query would do the job just fine. I'd be interested to see your table set up on this one.

Another note, I am not really a fan of using the auto-number field as a PK....it lacks a specific nature with regard to the actual data values that are relational, but that's certainly just an opinion...

 

[neileg]

Another note, I am not really a fan of using the auto-number field as a PK....it lacks a specific nature with regard to the actual data values that are relational, but that's certainly just an opinion...

That puts you in a minority! Most fields with meaning sooner or later turn out to be non-unique. But if it works for you...

 

[ajetrumpet]

Neileg,

I rarely use autonumber for my type of work, because there are a lot of fields I input data into that absolutely have to be unique...that probably is rare, I agree...it's dangerous too getting into that mode of thinking...

 

[smiller]

I have attached the database I am creating so you can take a look at it. Currently, all the base data is contained in "Lathe Master List" and "Mill Master List." The forms then that I will be using to input transactions are "Insert Request Form--Lathe*" and "Insert Request Form--Mill*." The assistants in the office who will be inserting the transactions will only use those two forms. There is a subform in each form that links to each part and records when it is inserted or removed. This is the only place where data will be inserted. From there I would like the values to the right of the subform to auto update. These values are stored in the respective master lists. Currently I have the subform linked to the form by parent to child relationships. I changed some things around, though, and now not all the in/out data is showing up in the form. I looked through the thread you supplied and I didn't quite see how it could help but I might just be missing something. I am not very good with access. And I do have a lot of repeat data like you mentioned. That was the only way I could think of to make sure everything linked to the appropriate part. I really don't need this repeat data if you know of an effective way to eliminate it but still keep all the relationships between the transactions and parts correct. The relationships are important so that the quantity numbers that auto update will remain correct and up to date. Thanks again for all your time. If I have left anything out or you have any other questions for me, please let me know.

It is not allowing me to attach the file. I am not sure if the size is too big (4 MB) or if I am doing it wrong. Can I directly upload the file or do I have to do it through a zip file? If a zip file is necessary, how should I go about doing that?

Thanks!!!

 

[ajetrumpet]

.zip

To zip a file, try a right click on the file icon....Send to>Compressed Folder. You can also zip a file through the program (I believe)...File Menu>??? Make sure you have a program to zip the file too!! You probably have WinZip with your OS though...you can always go into that program and zip it through browsing the directories too...

 

[smiller]

ok lets see if this works....

 

[ajetrumpet]

I looked through the thread you supplied and I didn't quite see how it could help but I might just be missing something. I am not very good with access.

That thread was just a reference, the other question was not as specific as yours is.

The forms then that I will be using to input transactions are "Insert Request Form--Lathe*" and "Insert Request Form--Mill*." The assistants in the office who will be inserting the transactions will only use those two forms. There is a subform in each form that links to each part and records when it is inserted or removed. From there I would like the values to the right of the subform to auto update.

I assume what I was looking at was basically an inventory control system. If this is the case, the form controls that you want to "auto-update" should be calculated fields that are updated on an event property of your choice.

The relationships are important so that the quantity numbers that auto update will remain correct and up to date.

You do not have any relationships set up between the Transation Inventory (In/Out/Scrap/etc..) fields and the "on-hand" inventory fields. Even if you did, you don't use relationships to accomplish what you want to do here.

Currently I have the subform linked to the form by parent to child relationships.

Actually, you don't...you have a few one-to-one relationships, and the PK's mean nothing because they are not the parents to any other fields. There is no parent-child relation in this DB.

I just realized that all the information that I have given to you sounds really negative. Well, it's not really, I just want to let you know what the facts are.

 

[ajetrumpet]

I think you have a great start on it. A couple more things...
Your subforms are not linked, because there is no parent-child link of your tables. Your main form and subform are just separate object based on your different tables. Notice...if you scroll through the main form, you will see the one-to-one linked fields scroll with each other, but none of the other fields will. Establishing a one-to-many will synchronize the entire record you are scrolling through instead of the fields. Here is what I would do if I were you....

1) Continue to use the autonumber in the Master tables for a PK, but dump it all together in the "in-out" tables. "In-out" should be your child tables. You could either do this or create a composite PK with GRADE, TYPE, SPEC#, and a couple other fields too that I can't remember. The reason you would do this is because you do not have one field that specifically identifies an entire record. I wouldn't recommend this method though.

2) Update your calculated fields (inventoryused, new, etc...) by writing mathematical expression queries. The fields needed to be updated will be stored in your query just like they are in your tables. And YES, there are legitimate reason why you don't store calculations in source tables. By calculating this way, the numbers will be changed via the ".requery" command on your form.

I do think you need to restructure you DB a bit first though. I could probably help you with if you'd like....this stuff is pretty hard to explain, especially to someone who is "not very good with Access" ")...

 

[neileg]

I'll be a little more direct that ajetrumpet. Your design is all wrong and I imagine you are reasonably experienced at using spreadsheets, 'cos that's what your design resembles. You need to understand normalisation so you can design this properly.

In basic terms, any form of inventory system needs to record transactions, not balances. So you have a table that records movements in your inventory items. Each record will contain the detail of the item and the amount of the movement together with the reason for the movement, date and so on. To get your balance you then sum these movements.

I suggest you do a search in these forums for inventory and stock control for lots of discussion and help. Be warned, inventory is a non-trivial subject, not ideal for cutting your database teeth on!

 

[smiller]

thanks so much for all the advise. If you don't mind reworking it and have time that would be awesome. I haven't touched it at all yet because I didn't want to push it anymore in the wrong direction. I'll look at all the instructions and play around with a copy of the database and see what I can do. Like I said before, I am pretty bad at this. I really appreciate all the help

 

[neileg]

thanks so much for all the advise. If you don't mind reworking it and have time that would be awesome. I haven't touched it at all yet because I didn't want to push it anymore in the wrong direction. I'll look at all the instructions and play around with a copy of the database and see what I can do. Like I said before, I am pretty bad at this. I really appreciate all the help

I don't know enough about the business processes to rework your design. Do the research and then come back with questions about what you still don't understand.

 

[ajetrumpet]

Smiller,

I can probably help you a little bit...are you saying that you would like me to do this??

Let me know, and I might be able to shoot a revision back to you in a couple days...

 

[smiller]

Ajetrumpet,

If you don't mind revising it some and setting me off in the right direction, I would greatly appreciate it. If you are too busy or just don't want to revise it, i understand. I have tried reading a book and other help notes but still greatly struggle. Thanks again for everything!

 

[ajetrumpet]

Smiller,

Tell me the meaning (definition) of the non-obvious fields in your database (i.e. Grade, Spec, Type) These can obviously be duplicated...I've seen it for almost every combination possible over the three fields...

 

[ajetrumpet]

Test

This is as far as I could get with it. You can see what avenue I am headed down to try and restructure for you. I think you should about it this way. There are cascading combos on the form (very simple) and an inventory calculation button. This is really the way I could think of doing it according to how your setup is right now. Take a look at the controls, queries and code in the VB editor.

Look at my archived articles too....they are in the .xls file.

 

[ajetrumpet]

oops...sorry!

 

[smiller]

here is the database with the current master list tables