Hi all, access noob checking in again. I solved my last problem through google, but I fear this one will be a bit more complicated, so I'm hoping for any help I can get.
So I have a database for a project that is simulating a used bookstore's database system.
My relationship table is attached below. Please forgive the lack of normalization on the 3 type of employees on the right side, it's a specific project requirement. I understand it's not good but I don't foresee an issue for this particular form.
I'm trying to create a form that will work for a student bringing in their books to sell to us. Each order can contain multiple books. People cannot bring more than 1 of the same book nor more than 5 books.
I would need to create a record in purchase_inventory (I have been working on this part for a bit, so far I managed to autonumber purchase# through dmax+1 to avoid using the autonumber in the table itself) and also records in the junction table [purchase_detail] depending on how many book is brought in, and also update the inventory table.
You can assume all student_ID infomation, everything in the inventory table are pre-set up.
For example:
Student 1 brings in book A to sell. This is my second purchase.
I need to create a record in purchase_inventory:
Purchase#: 2 (Default value, populated through dmax)
SR_ID: (Manual input based on employee serving customer)
Student_ID: (Manual input, should validate against SAF_Student_List)
Purchase_Date: Auto populate through default
Purchase_Time: Auto populate through default
Sold_Status: Set to unsold automatically
[Purchase_Detail]: 1 record per book brought in, joint primary key of purchase# and ISBN
Purchase_Price: Should be populated through a lookup in the inventory table
Purchase_Quantity: Ignore, will auto populate as 1
Book_Condition: Drop-down menu with several options
[Inventory]: Master file, must update quantity_on_hand based on detail given in junction table.
I'm not sure where to start with this. I've always done simple forms by creating a bound form and just updating fields. My challenge is that I have to create a single record in 1 table, possibly multiple records in another, and then update a master table, all in 1 form. Is this do-able, or is this too ambitious for a access noob? From my classes I understand I'll need to use some subforms, but we've only learned theory and we were more or less thrown into this access project head first.
Thanks in advance for the help, please ask if you need clarification regarding my problem.