developing vendor quote database 2

[Sheelyah]

Forgive my ignorance, this is my first post and I am a relatively new MS Access user. I am trying to develop a MS Access database application to process multiple vendor quotes and manage the process of comparison/selection and tracking of products and pricing. I have designed the flow and I know what I want to get out of the database (metrics, reporting), but when I looked through the Microsoft templates, I didn't see one that was close to what I need.

I have a list of all the fields I'd like to use and an idea of the report(s) I want to see, but I want to get a second (third, fourth?) opinion in case there is something critical that I may be overlooking in the design.

I am looking for suggestions or if anyone has a database template that may be customizable by a novice, I'd certainly appreciate the help.

 

[fneily]

Seems like only three tables: Vendor, Products, VenProdPric
Vendor table has the appropriate info of the suppliers.
Products table has obviously product info like description.
VenProdPrice looks like this:
VPPID
VendorID
ProductID
Price

VPPID is primary key of VenProdPrice table
VendorID is primary key of Vendor table
ProductID is primary key of Product table

All your analysis falls right out. If this is your first Access database, bone up on Normalization and Relationships.
This is so simplistic, you don't need a template. Unless you forgot to tell us about more data.

 

[ssatech]

Why not use the Orders Management Database Template:

http://office.microsoft.com/en-us/templates/TC010185481033.aspx?CategoryID=CT011366681033

Modify for your own use:

Customer = Vendor
Orders = Quotes
Order Details = Quote Details
Products = Products

 

[Sheelyah]

Thanks, Fneily and SSATech... Both good concepts. I looked at the Orders Mgmt database as well. Let me better explain the elements needed within the database:



Project # (primary key – automated)
Requestor
Organization (drop-down field)
Approver (drop-down field)
Title
Received Date
RFQ Date
Due Date
Status
Category
Comments
PR #
Disposition (drown-down field)
Vendors Data:
Company (drop-down field)
Bid (one-line text field of either "no-bid" or total $$)
Bid Submitted Date
Bid Won

I want to be able to pull reports based on:
Requesting organization
Category
Status
Bid won
All quotes received by record number across vendors that list their total quote, title and quote #.

How complex does that sound?

 

[ssatech]

How bout the following:
tblOrganization - OrganizationID (Primary/Autonumber)
tblApprover - ApproverID (Primary/Autonumber)
tblStatus - StatusID (Primary/Autonumber)
tblCategory - CategoryID (Primary/Autonumber)
tblDisposition - DispositionID (Primary/Autonumber)

tblProject - ProjectID (Primary/Autonumber)
Requestor - Text
OrganizationID - Number
ApproverID - Number
Title - Text
ReceivedDate - Short Date/ Format: 99/99/00
RFQ Date - Short Date/ Format: 99/99/00
Due Date - Short Date/ Format: 99/99/00
StatusID - Number
Category ID - Number
Comments - Memo
PRNumber - Text or Number
DispositionID - Number

tblProjectDetails - ProjectDetailID - Primary/Autonumber
ProjectID - Number
VendorID - Number
Bid - Text
BidSubmitDate - Short Date
BidWon - Yes/No, Value List: "Yes";"No"

tblVendors - VendorID - Primary/Autonumber
CompanyName - Text


Relationship:
tblOrganization - 1:M (OrganizationID)- tblProject
tblApprover - 1:M - (ApproverID)-tblProject
tblStatus - 1:M - (StatusID)-tblProject
tblCategory - 1:M - (CategoryID)-tblProject
tblDisposition - 1:M - (DispositionID)-tblProject

tblProject - 1:M - (ProjectID) - tblProjectDetails

tblVendor - 1:M - (VendorID) - tblProjectDetails

Reports: Based it on a query like -
SELECT Project.OrganizationID, Project.Requestor, Project.ApproverID, Project.Title, Project.ReceivedDate, Project.RFQDate, Project.DueDate, Project.CategoryID, Project.StatusID, Project.PRNumber, Project.DispositionID, [Project Details].ProjectID, [Project Details].VendorID, [Project Details].Bid, [Project Details].BidSubmittedDate, [Project Details].BidWon
FROM Project INNER JOIN [Project Details] ON Project.ProjectID = [Project Details].ProjectID;

Use the criteria to filter for:
-Company
-Bid
-Bid Submitted Date
-Bid Won

Then build your reports for those criteria.

***Take a look again at the "Orders Managementt Database Template" and see how this principle may be applied....

e.g. You could use the same "Add and Order and Details" Form with just a few modifications to suit your need...