I am so frustratingly stuck

[nicole.skeeters]

I've come to you guys for help in the past few weeks while trying to build this database and even after a week long vacation I am about to pull my hair out. I downloaded, as suggested, a template for an inventory database that I thought would help. While it did some, there is still so much not talking to the right parts that 90% of my forms or reports don't work. So I'm going to start back at the drawing board, asking for help with design this time.

These are my goals:

• To keep a current, live look at inventory by entering in new supply requests and subsequently entering in received stock
• To be able to show, over time, how much each division/department/supervisor is requesting in a specified time frame (ie quarterly)

To achieve this, I realize that I need the following:

• A form that enters supply requests - a single transaction id for a single supervisor at a time that records multiple supplies coming out of inventory and updates the current stock accordingly
• A form that enters stock received to update current stock data
• A report to show amounts ordered ($) by each division/department
• A report to show inventory reorder requirements

All that being said, I think my biggest issue is and has been relationships. I understand technically how to make these forms and how to design the reports etc. I just for some reason cannot tie the two pieces of knowledge together to make this database work - I dont know what to connect to what. I know that my entry form needs to create a record in my Inventory Transactions table for every new transaction id (how do I get the same id to show up on multiple lines!) and then, because of the transaction type, needs to tell my inventory table that the quantity has been reduced by x amount. I would like, as well, for there to be a message if I choose an item that is out of stock. Same with the restock, minus the transaction id. If I could get all that data to flow, I am confident that I can build reports to calculate and show what I need to. I've attached a copy of the database where I have removed supervisor personal information.

Any help or suggestions would be most appreciated. Thanks in advance.
Nicole

[orange]

Work through this tutorial.
http://www.rogersaccesslibrary.com/T...lationship.zip

You must get your tables and relationships (data model) set up and tested before moving to forms, reports etc.
Tables and relationships are fundamental --if these are not correct (do not match your business) then you will spend countless hours trying to "work around" a design issue.

Make up some test data; test your data model. If something "doesn't fit" then review your model; review your test data; and repeat as necessary.
Good luck.

[ssanfu]

@Nicole,
I can see why you are so frustrated. Looking at that inventory template makes me, well....

Spaces and special characters in names, tons of macros, yuck!!!!

I did see you changed the "Employee" table to "Supervisors" (IIRC) - somehow the "Last Name" field was deleted.

So I'm going to start back at the drawing board, asking for help with design this time.

What table structure do you have so far??

[nicole.skeeters]

Hi there,

Yes, most of that was built by microsoft and not myself. I deleted the "last name" field to protect the innocent - hah! I did have a meeting this afternoon with two IT/access folks, but quite frankly we gained little ground. I've attached a screen shot of my table structures. The relationships had all been deleted in a last second effort to make the dang Inventory Transactions List form actually work (says it cant find a record in the table 'inventory' with key matching field 'item id').

Thanks for your help.

@orange - the article was very informing, thank you. Sadly I'm still having troubles, but it was a great resource!


Nicole

[nicole.skeeters]

Ok, here's where I stand at the moment. The form "inventory transactions list" is based upon a query called "inventory transactions extended." This query is pulling from tables "inventory," "inventory transactions," and "transaction types" where currently only "inventory transactions" and "transaction types" are joined via the field "transaction type id," an autonumber field. This is producing 1,188 results though in tbl "inventory transactions" I only have four dummy records. I know this is due to the missing join between "inventory" and "inventory transactions." HOWEVER! I cannot figure out how to join these two tables. The obvious, and likely correct choice should be Item ID. However, in tbl "inventory," Item ID is a text field due to it holding both letters and numbers (it references catalog numbers). The field Item ID in tbl "inventory transactions" is a number field. The most logical thing to do, would be to change the Item ID field in tbl "inventory transactions" to a text field. When fields are matched and the join created by that field between tbl "inventory" and tbl "inventory transactions," the query no longer pulls results. At all. I dont understand what the failure is. Please help!!

[ssanfu]

I looked at your dB Sun night and made changes, but it turns out I was using 2010 and the dB couldn't/wouldn't open in 2007. I haven't had time to look at it again.

HOWEVER! I cannot figure out how to join these two tables. The obvious, and likely correct choice should be Item ID. However, in tbl "inventory," Item ID is a text field due to it holding both letters and numbers (it references catalog numbers). The field Item ID in tbl "inventory transactions" is a number field.

I changed [inventory transactions].[Item ID] to a long. (Linking on text fields can be done, but it is way slower than linking on longs.)

Attached is something I threw together (A2000).. No data...... maybe it will help....

[nicole.skeeters]

Thanks for that ssanfu. I'm not quite sure what you mean by changing [inventory transactions].[item id] to a long. I'm thinking you mean, number: long integer...but I could be wrong?

[ssanfu]

Yes.... I used short hand... Long means number, long integer.



You also might want to read this link at Allen Browne's site
http://allenbrowne.com/AppInventory.html