One Requisition with multiple line orders! Please help me

[Abouya]

Hello Everyone,
I've tried for weeks to figure out this, but no solutions yet!
I thought this going to be easy, but i was mistaken. I tried reading and watching many tutorials but i failed miserably. This database is for helping managers fill in order requisition forms. Each requisition has a unique ID auto number. Under each requisition ID we have all the info for the vendor, requisitioner, project number...etc. Each requisition should hold different line orders. for this, i created a query to allow multiple entries. Like line 1 then all the related order information like quantity, item number, tax, delivery date, then EXTPrice(Total line price). The PO should be optional entry, if they have one they can enter it.

I started this database for the 6th time from scratch and i properly named the tables and all that. I created table relationship.

Could someone with a kind heart please help me out! I'm losing faith.:banghead:

 

[jdraw]

Tutorials:
Entity Relationship Diagramming
Class information
Catering Business
Consolidated Widgets

These are from Rogers Access Library other links on this page serve as excellent references. Especially Normalization and the 12 steps/nutshell article.

Each tutorial should take about 30-60 minutes---less after you understand the process.

Also, here are some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

Intro to Database
The Relational Model
Data Modelling and the ER Model

Here is a different set of videos by a different presenter to complement those above.
These are free youtube videos from Mark Serva that deal with entity relationships, modelling, cardinality, ....
The playlist is listed here.


Good luck with your project.

 

[Uncle Gizmo]

Your sample database doesn't work straight out of the box. I suggest you repost it in working order!

 

[jdraw]

I suggest you describe the associated business and business processes to readers in plain English.
Can you walk us through the business process to create a new PO?

 

[Abouya]

Thanks all of you for replying. I really appreciate your help. This database is a simple order requisition. Managers will be able to fill a main form called requisition that will hold as many order lines as they wish. This is from start to finish the process i'm looking for:

An automated number will be assigned to requisitionID, then a user will fill his name(requisitor) that has a spending limit that it will be displayed using a combo box. Then he will fill the company he is buying from. A combo box will be used to fill in vendorID, remit to code for payment etc. then fill po filed if he knows it, if not he can leave it empty (since this is not po creation, all this will take place before creating a PO) (PO will be assigned later when he fills his requisition and will be handeled by an ERP system.

A query that will have all field from orderRequisitionDetails will allow multiple line/orders that all under one previously automatically assigned requisitionID. As soon as he start filling item number, number 1 should appear automatically under the line field in the query subfield. After filling all the first line, line number 2 will appear allowing to fill another order from the same vendor. Total of all these lines is the total of the 1/one requisition number ID.

I hope this makes sense, i'm just looking to modify my tables and relationship to allow serving the purpose i described above.

Again, thank you so much for your help. I did many tries but i always mess up everything.

 

[Abouya]

Your sample database doesn't work straight out of the box. I suggest you repost it in working order!

I created this database 6 times but i always ended up failing miserably. I'm trying to readjust my tables and relationship before even implementing a form. I just gave an explanation of how the process should work. Thank you.

 

[Uncle Gizmo]

There is a free sample database provided by Microsoft Called The Northwind Database. You can download it from here:-

Northwind Downloads:- https://sites.google.com/site/msacc...wf-vip/northwind-database/northwind-downloads

Note my comments... I don't know if the links are safe use your own judgement.

I mention this database as it is basically an invoicing system, and that's just about the same as what you are describing. This sample has much of the functionality you require. I believe you should be able to find other posts on the Internet about using this northwind database although I haven't actually looked myself.

As I said it's a sample database provided free by Microsoft, it's not intended to be put into a production environment and it has some basic flaws which I point out in this set of videos HERE:-

Northwind Problem and Solution -Videos:- https://sites.google.com/site/msacc...hwind-database/northwind-problem-and-solution

I suggest you look at these videos before you start using it in earnest...