Windows 7 64bit 
Access 2010 64bit
Hi guys, this is my first post on these forums.
I used Access 2004 intermittently in 2004, as part of a computer programming curriculum. At the time I loved working with Access, and database design came naturally. Long story short, I haven't used access or designed a database in about 6 years, and my knowledge was pretty limited even then. However, I understand the principles of table design and SQL.
I am producing a small database to assist in recording material received on a construction site. I realize that I could probably do all of this much easier with an excel spreadsheet, but I am very interested in databases still, and I wanted to produce something, refresh my knowledge, and be able to produce basic reports showing what is in stock, etc.
I have planned out the table structures, and set the relationships (see image attached) as I think they should be set up.
A large table called "ITEMS" which contains all items being used on the job, with associated description fields.
This table is linked the "MRR" (Material Receiving Report) table through a junction table called "ITEM_LOOKUP", since most MRR's will contain multiple items. I realize Access can now do multiple items in a single field somehow, but I prefer to use the old method of using a junction (lookup?) table.
The MRR table in this case is the "Master" table that truly ties the rest of the tables together. The MRR table is linked with foreign keys to the SHIPPER, PO, VENDOR, and WAYBILL tables, all through Many to One relationships. There will never be more than one shipper, Purchase order, Vendor, or shipper for any given MRR and set of items, but MRR's could come in with only half a PO worth of items and thus a second MRR will be generated later for the remainder.
Ideally, I was hoping to design a form with all MRR related tables except ITEMS displayed on the Main form, with a subform displaying the items associated with a given MRR. I was hoping to enable this form to allow a user to either a) Input a new MRR, including new Vendor/Shipper/PO/Waybill information from scratch OR b) Select a Vendor / Shipper with dropdown boxes which will automatically fill the address etc information.
After trying this for about 3 hours, I was just getting frustrated with the problems of trying to design a form that loads history information for use as a new record and also lets me save information that is totally new.
It feels like I should just get rid of the extra fields below "Vendor" and "Shipper" on my form and just force the user to choose a vendor by name.. but what if I have 2 vendor entries with the same name and different addresses ? (Not uncommon).
I was able to get the combo boxes for Vendor Name and Shipper Name to work (they seem to look up the correct values), but selecting a value did not populate the below fields for address, contact, etc.- it would just keep the values and only change the vendor name- until you reload the form.
The other problem right now, is that the information for "Driver" "Waybill" ect will be different every time I input a new MRR, so I cannot rely on combo boxes and pre-entered records to load this information. However, the way I have things set up right now, entering data into these columns populates the "WAYBILL" table, but does not link the WAYBILL_ID to MRR_WAYBILL_ID- the MRR table does not populate this field, and for this reason, when i use the MRR form again, I can't even see my new MRR record, because the SQL query that populates my form does not pick up on a new record that doesn't referentially line up with a WAYBILL_ID.
I don't think there is anything wrong with my relationships, just my form implementation (or maybe my overall "design" needs adjustment). Should I be writing code to make up for these problems?
Thanks for any help you guys can provide. I have included a screenshot of my relationships, and a project file with where I am at currently, populated with placeholder data.
Reply With Quote
