UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Inventory Coffee, Any Version    
 
   
osmith96
post Oct 11 2019, 12:09 PM
Post#1



Posts: 14
Joined: 25-June 19



Hello guys!

If anyone could help me get this database started I would appreciate it for some reason today my mind is going blank.

I'm attaching the database I am working on here.

So this is a simple database, it is pretty much a coffee inventory and distribution database. We give out coffee to various departments within our organization and we want to keep track of each transaction and keep track of our inventory for each coffee. I can't seem to get a form set up correctly.

I want this form to begin with a user selecting a department(tblDepartment), then next the user will enter the the details about this transaction(tblTransaction) which will be the date of the transaction and who is noting it, and then the details about the transaction(tblTransactionDetails) the coffee that is selected and how much of the coffee that is selected and once the form is submitted I want the quantity to be subtracted out from the inventory(tblInventory).

But some reason everytime I make my form I can't get it to function the way I want it.

If anyone is available to help me with this, I would appreciate it any tips, etc.

<Link Removed>



This post has been edited by DougY: Oct 11 2019, 01:34 PM
Reason for edit: Linked removed. Please upload the sample database to the post directly. Thanks, Doug

Attached File(s)
Attached File  Capture.JPG ( 32.4K )Number of downloads: 14
 
Go to the top of the page
 
RJD
post Oct 11 2019, 01:28 PM
Post#2


UtterAccess VIP
Posts: 10,130
Joined: 25-October 10
From: Gulf South USA


Hi: I think most here will not connect to Google drive to see the db. Perhaps you could compact and zip the db and attach it to a new post. If the resulting zip file is larger than 2MB, perhaps you could cut the db down to only relevant objects and enough non-sensitive data to work with, re-compact and re-zip.

Thanks,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
osmith96
post Oct 11 2019, 02:41 PM
Post#3



Posts: 14
Joined: 25-June 19



Thank you for responding.
I have compressed it and attached it here.
This post has been edited by osmith96: Oct 11 2019, 02:44 PM
Attached File(s)
Attached File  CoffeeLog.zip ( 253.03K )Number of downloads: 10
 
Go to the top of the page
 
MadPiet
post Oct 11 2019, 04:57 PM
Post#4



Posts: 3,358
Joined: 27-February 09



QUOTE
I want this form to begin with a user selecting a department(tblDepartment), then next the user will enter the the details about this transaction(tblTransaction) which will be the date of the transaction and who is noting it, and then the details about the transaction(tblTransactionDetails) the coffee that is selected and how much of the coffee that is selected and once the form is submitted I want the quantity to be subtracted out from the inventory(tblInventory).


Oh, so you basically want a bank balance type thing for each type of coffee? how do you increase the amount on hand? if you're modeling something like a company that buys and resells something, you have PurchaseOrders for product and then invoices for sales of that product. And then you can determine stock at any point in time doing a running total of PO Quantities - invoice.Quantities. Banks sort of do that with your bank account, but they store a new starting balance every month.
Go to the top of the page
 
osmith96
post Oct 11 2019, 06:44 PM
Post#5



Posts: 14
Joined: 25-June 19



No I just want to keep record of how we are distributing our coffee(k-cups) through various departments.

So for example we distribute coffee to the IT department, the user who is inputting this information would select the IT department, then enter the details (date of distribution, and who distributed) then the user will select the coffee and how much of the coffee was distributed to that department.


All the inventory is stored in the table tblInventory and with each distribution I want it to be took out of the inventory.
Go to the top of the page
 
osmith96
post Oct 11 2019, 07:02 PM
Post#6



Posts: 14
Joined: 25-June 19



Hey guys I figured out what I need to do. It's been a long day lol. Thank you to everyone who helped out.
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2019, 07:10 PM
Post#7


UA Admin
Posts: 36,033
Joined: 20-June 02
From: Newcastle, WA


As a general principle, when you have a relationship between tables, the data entry forms for that will be main form/sub form. The main form is bound to the table on the one-side of the relationship. The sub form is bound to the records in the table on the many-side of that relationship. If you have more than one many-side table, each of them would be bound to a separate subform.

So, the guideline would be:

One form bound to each table. No queries joining multiple tables should be used for data entry forms.

It looks that that outline would apply to the tables here. Give that a try.

Create a form bound to the table in which you track transactions. Add a subform for the table bound to the transaction details table. Other tables, which provide lookups for things like departments, can be used to provide row sources for combo boxes on the appropriate form.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
osmith96
post Oct 11 2019, 09:05 PM
Post#8



Posts: 14
Joined: 25-June 19



Hey Grover,

That’s exactly what I did, I just magically forgot how to start a database all of the sudden lol. It all came back thanks for the tips.
Go to the top of the page
 
GroverParkGeorge
post Oct 12 2019, 08:40 AM
Post#9


UA Admin
Posts: 36,033
Joined: 20-June 02
From: Newcastle, WA


You're welcome.

I know that feeling. "I've done this before, now how did I go about it?"

Continued success with your project.

George

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 02:36 PM