Confused with relating tables (1 Viewer)

[Steelman]

I am a buyer in a small purchasing department and in the process of creating a database to use to track RFQ's (Requests for Quote) that we send to our suppliers. Describing the process; When we need to get a quote for an item or items we need to purchase we create a RFQ that has information like RFQ Number, Date, Buyer Name, Due Date, Originator, Project Name. Each RFQ will go to multiple suppliers and it will also have multiple lines that allows us to enter multiple items. When we get the RFQ's back from our supplier we enter the cost from each supplier for each item that was on the RFQ. I am looking for help in setting up the relationships.

So for I have the following tables:

RFQtbl
RFQID
Date
Buyer
DueDate
Originator
ProjectName

RFQLinetbl
RFQLineID
RFQLineNumber
ItemPartNumber
ItemDrawingNumber
Quantity
Price

Suppliertbl
SupplierID
SupplierName
Salesperson

I know I need to add fields to some of these tables so I can relate them but I am stuck given I have muliple Suppliers and multiple Items for each RFQ.

Any help would be apprecaited. I am new to Access so pardon my inexperience!

 

[David Eagar]

I think what you need is a item / supplier table so that you could use cascading combo boxes when adding items to a purchase request ie when you select an item, you then choose from the available suppliers for that item

 

[WIS]

I am a buyer in a small purchasing department and in the process of creating a database to use to track RFQ's (Requests for Quote) that we send to our suppliers. Describing the process; When we need to get a quote for an item or items we need to purchase we create a RFQ that has information like RFQ Number, Date, Buyer Name, Due Date, Originator, Project Name. Each RFQ will go to multiple suppliers and it will also have multiple lines that allows us to enter multiple items. When we get the RFQ's back from our supplier we enter the cost from each supplier for each item that was on the RFQ. I am looking for help in setting up the relationships.

So for I have the following tables:

RFQtbl
RFQID
Date
Buyer
DueDate
Originator
ProjectName

RFQLinetbl
RFQLineID
RFQLineNumber
ItemPartNumber
ItemDrawingNumber
Quantity
Price

Suppliertbl
SupplierID
SupplierName
Salesperson

I know I need to add fields to some of these tables so I can relate them but I am stuck given I have muliple Suppliers and multiple Items for each RFQ.

Any help would be apprecaited. I am new to Access so pardon my inexperience!

If I understand correctly from what you have described (1 RFQ can have many Lines and many Suppliers), you will need RFQID in the other 2 tbls as Number, Long, Duplicates Allowed. RFQID in the RFQtbl should be Autonumber, No Duplicates Allowed.

This will allow you to set 1:many relationships between the 3 tbls. If you need to set a relationship between the 2 many:many tbls you will need to set up another tbl with RQFLineID and SupplierID as the 2 flds.

Naming: Std namimg convention is "tblXXXX" rather than "XXXXtbl".

Hope this helps

 

[speakers_86]

If RFQLinetbl is your junction table, then if you just add 2 fields, SupplierID and RFQID, you should be fine, assuming I understood what you are trying to do.

 

[Steelman]

If I understand correctly from what you have described (1 RFQ can have many Lines and many Suppliers), you will need RFQID in the other 2 tbls as Number, Long, Duplicates Allowed. RFQID in the RFQtbl should be Autonumber, No Duplicates Allowed.

This will allow you to set 1:many relationships between the 3 tbls. If you need to set a relationship between the 2 many:many tbls you will need to set up another tbl with RQFLineID and SupplierID as the 2 flds.

Naming: Std namimg convention is "tblXXXX" rather than "XXXXtbl".

Hope this helps

WIS, I think you are understanding, 1 RFQ can have many lines and many suppliers. I have never worked with many to many tables but will play around with it and post back if (when) I run into more help needed.

I am pretty much only able to access at work so my responses may be slow in coming.

Thanks!

 

[Steelman]

So now I have the table structure as follows:

tblRFQ
RFQID
Date
Buyer
DueDate
Originator
ProjectName

tblRFQLine
RFQLineID
RFQID
RFQLineNumber
PartNumber
DrawingNumber
Quantity
Price

tblSupplier
SupplierID
RFQID
SupplierName
Salesperson

I have created a one:many relationships between the tblRFQ and tblRFQLine and between tblRFQ and tblSupplier. I don't think I am understanding your comment "If you need to set a relationship between the 2 many:many tbls you will need to set up another tbl with RQFLineID and SupplierID as the 2 flds." Can you explain further what that will do for me?

Many thanks!

 

[WIS]

So now I have the table structure as follows:

tblRFQ
RFQID
Date
Buyer
DueDate
Originator
ProjectName

tblRFQLine
RFQLineID
RFQID
RFQLineNumber
PartNumber
DrawingNumber
Quantity
Price

tblSupplier
SupplierID
RFQID
SupplierName
Salesperson

I have created a one:many relationships between the tblRFQ and tblRFQLine and between tblRFQ and tblSupplier. I don't think I am understanding your comment "If you need to set a relationship between the 2 many:many tbls you will need to set up another tbl with RQFLineID and SupplierID as the 2 flds." Can you explain further what that will do for me?

Many thanks!

"If RFQLinetbl is your junction table, then if you just add 2 fields, SupplierID and RFQID"

I think speakers 86 might be right. Put SupplierID into the line tbl and you won't need RFQID in the Supplier tbl. RFQ tbl to Line tbl is a 1:many (RFQID)and Supplier tbl to Line tbl is a 1:many (SupplierID).

A typical situation where you need the 3rd tbl is doctors/patients in a large practice/hospital. One dr can have many patients and 1 patient can have many drs. Here you need to set up a 3rd tbl with DoctorID and PatientID, but your scenario doesn't quite match that.

 

[Steelman]

This is all very confusing to me so bear with me.

Since I want to create an RFQ where I select multiple suppliers and also want to add multiple items for the suppliers to quote wouldn't it make more sense to use the tblRFQ as the junction table rather than the tblRFQLine. I am looking down the road to creating the form in which to input the data. I envision creating my RFQ, selecting my suppliers then adding the lines for the items that I want to have quoted. I would then print a report which would be a page with all of the header data contained in the tblRFQ record and the line data associated with that RFQ following. One report for each supplier that I had chosen when setting up the form.

Does this make sense?

Thanks!

 

[The_Doc_Man]

You have issues with your design. I don't think your tables fully match the real world.

First, you have an RFQ. An RFQ can contain line items. Therefore, parent/child relationship between RFQ and its items. RFQID in parent is Prime Key (PK), RFQID in line item table is foreign key (FK). One RFQ, many line items.

Next you have suppliers. One might guess two possible situations.

(1) Supplier can supply everything on the RFQ
(2) Supplier cannot supply everything on the RFQ.

This leads you to a business rules issue: Do you allow a supplier to bid on part of an RFQ? This will have structural impact on how those tables are linked. If the supplier can only bid on whole RFQs, you need a junction table between your suppliers and the RFQ. If you allow fractional responses, the junction is between the suppliers and the line item table.

Next issue: Suppliers typically offer price breaks and other price incentives to get you to buy their services/products. So putting a cost factor in the RFQ table or the line-item table doesn't reflect reality. You need a vendor's proposal to your RFQ, which in my business is a Quote. The prices are in line items of the quote, not line items of the RFQ. The ONLY reason you would have prices in the line items of the RFQ is to record the prices of the winning bid or bids, particularly if you allowed fractional response to an RFQ.

Next question: Are you separately tracking Quotes or do they vanish if they aren't the winners of the competition for your business dollar/euro/pound/whatever? This relates to how you treat the quotes and quote line item tables.

Next question: Can vendors bid on more than one RFQ at a time? This might lead to the need for a junction table between supplier and RFQ. If you allow partial quotes, this structure gets incredibly more complex. (But complex or not, it has to match your business rules.)

I submit for your consideration that you are confused for two reasons, not one. First reason: You are new to Access. (We all were, at one time.) SECOND reason: You must fully explore the problem you are programming because of the Old Programmer's Rule #1 - you can't do it in Access if you can't do it on paper. (Meaning: You have to know your business rules inside-out in order to program the business model in Access.)

Another question, and this is equally important. You are beginning to delve into real-world business models that work best only when you have an understanding of normalization. Using your favorite browser, search for articles to help you.

First, read the Access Help on normalization.

Second, visit www.wikipedia.org and read the article on Database Normalization.

Third, search the internet for articles on Database Normalization. In this search, only read those articles from universities or vendors whose names you recognize. When you have read enough of these articles that the next two you read contain nothing at all new to you, you are ready. Despite the complexity of my three-point reading program, it might not take you even a full afternoon to reach this point.

 

[Steelman]

Doc_Man,

You put a lot of thought into your response and I appreciate it very much. You seem to have a pretty good idea of my process so I will make a few comments and try to answer a few of your questions.

I am setting up the application with the assumption that Situation 1: "Supplier can supply everything on the RFQ" is a given. If I have a situation where a supplier cannot supply one of the items, I would simply enter their quoted price at zero. Therefore I would assume this means that I need to set up a junction between the Supplier and RFQ tables. In my example db I have only indicated one price field, when in fact my plan is to have perhaps 5 or so quantity fields and 5 or so corresponding price fields. For the db design I am trying to keep it simple until I understand how to construct the db. I have two goals for this db. One is to make it easier to create a RFQ Form (report?) that I can send out to each supplier that I have chosen to provide me with a quote rather than doing it by hand. Two is to later have the means of search the db for different based on different parameters on all of the previous quotes that I have created.

When I receive back the RFQ form from the Supplier I will enter his quotes into the applicable price fields. There are situations where I am send out multiple RFQ's to a supplier but they will be handled individually and I will update each RFQ individually as I receive back their quotes.

I am definately new to Access and what little I know is self taught and therefore I probably know just enough to violate every programmer rule out there. I do understand what it is I want even if it appears I may not, but I may be having more of an issue explain my needs rather then knowing what I need.

I will take your advice on reading up on normalization and see it that can take me to the next level of questions.

Again, thanks!

 

[Steelman]

Whoa! After reading the normalization links I realize how much I do not know. Regardless I am not disheartened and will attempt to stay the course. The db structure I gave in an earlier post is a truncated verison of what I eventually intend to use and I decided to pare it down futher just to keep things as simple (at least for me) as possible until I understand the concept of what it is I am trying to do. Later when I get all of the forms and reports working to my satisfaction I will add the descriptive fields needed to make the db meaningful. My tables now have the following form and I have attached a pic of my relationships, as I understand they should be, based on Doc_Man's comments.

tblRFQ
RFQID
Date
BuyerName

tblRFQLine
RFQLineID
RFQID
ItemDescription
Quantity
Price

tblSupplier
SupplierID
SupplierName

tblRFQSupplierJT
RFQSupplierJTID
RFQID
SupplierID


If my tables and relationships are set up correctly I would like to next create a form for which I can add data.

Am I headed in the right direction?

Thanks!

 

[The_Doc_Man]

Well, immediately in response to my questions, you resolved a question about business rules, which then translated into a definite choice for linkages, one that removes a small ambiguity. So YES emphatically this is a good direction.

A very fine point and this is not even as severe as being lashed with a wet noodle. It is common for most of us to keep field names constant across linkages. So where you had RFQLineIDFK, you could have just used RFQID as the foreign key to that RFQ table. This is an extremely fine point that you only learn if someone tells you, so please don't take it as intended as a criticism.

Now we come to another set of questions. If a vendor must bid on your RFQ that has line items, do you care about anything other than total price? Do you wish to capture the vendor's line-item prices? Do you need to capture other details like tax rate, shipping rate, etc?

I ask this because of another Old Programmer's Rule: Access won't tell you anything you didn't tell it first. (Meaning: If you ever want to run a query on all XYZ in your database, you have to STORE the XYZ in your database.) So if you want to compare line items from one vendor to the next, you have to store...

Now, here's how you think your way through this. Here's me, mulling over the design of the problem and thinking out loud: OK, I've got some RFQs. Many suppliers can give me Quotes on multiple RFQs (which is a GOOD thing.) But I need a way to track apples to apples on the quotes. So I need to track costs somehow. Well, I can't put that on the RFQ until I accept a quote, so it doesn't go there. I can't put it in the the RFQLine table for the same reason. And it doesn't go on the vendor table because that vendor might have more than one quote pending. But wait, there is one place that is specific to THIS vendor and THIS RFQ - the junction table. So if I'm going to track price, tack it on to the junction table that is specific to both RFQ and Vendor at once.

See how that works? This choice, by the way, was made to be consistent with 3rd normal form issues. The price on quote X from vendor Y depends on a PAIR of keys, so it has to go in the table where those keys appear together.

Only you know where you are going. The words in this forum won't give us the understanding that you have about your business rules and environment and local govt. regulations and such. Which is why the question "Am I going the right way" will be answered with generalities. Only YOU can know if your model and your Access DB are aligning with each other.

You'll get the best out of this forum by asking for pointers to specific tidbits of information rather than general discourses on theory of normalization. (E.g. I pointed you to a topic and said "Search for it" rather than re-inventing that wheel.) I'll make another suggestion: Learn to search the forum. When you are at the top of the page, there is a ribbon on that page with the word SEARCH and a down-arrow, third in from the right. We might tell you to search this forum for some keyword. Practise that.

 

[Steelman]

Great feedback, continued thanks!

Believe me, no criticism taken on anything you may wish to offer. Like I indicated before, being new and self taught I'm prime for breaking conventions until otherwise told. Given this, I have made corrections to my foreign key field names.

Now, regarding your mulling! My intent with this RFQ process is to get back quotes from each supplier and then to enter thier price for each line item. I would then hope to be able to search later to find all of the quoted costs from each of the suppliers for each item. For example; say I would like to find all of the quotes received for part number ABC, regardless of how may suppliers quoted it or how many times I had them quote it. (There are situations where I may have the same item quoted more than once). I was originally thinking that I would be able to do this in the line item price field but am now realizing that I will not.

In your suggestion to add a price field to the junction table; I don't see how this would allow me to tie each supplier price to each line item quote. Perhaps I need another junction table between Supplier table and the RFQLine table. But this is getting a little fuzzy for me.

Also, I totally understand the forum search concept and did quite a bit of searching before finally deciding to post. Once I have my tables set up I will go back to searching as I try to tackle the forms that I will need. With so much information out there it is mind boggling to try to find the exact same question/reply that may have already been asked a million times.

Regards!

 

[Steelman]

Any other thoughts?

 

[The_Doc_Man]

If you care about line-item costs, they belong in a line-item table that is a child of the Quote table. Here's the thought process of coming to that decision.

I've got this price for a thing that someone wants to sell me as part of this quote. So where do I put the price to remember it? Well, it can't go to the RFQ line item until I accept the price, and I've got more than one set of prices to examine so I can't develop that linkage. The price came from the vendor, but more than one item is on the quote so I have to keep this at a level that shows me details. Oh, wait, there is a line item table for the Quote table that tells me price and unit price and quantity and etc. There is a good place for it...

And before you ask, this is an exact example of the Old Programmer's rule about Access not telling you anything you didn't tell it first. If you want to capture individual prices on quote items and they vary from vendor to vendor, you need to record them from vendor to vendor for each line item. Capture what you want to see.

Now, if the line-item part numbers can be somehow made to align, you could even develop a report that compares prices line-item by line-item. Another case of storing what you want to see. In this case, storing part numbers TWICE - once in the RFQ line item table and once in the Quote line item table.

A fine point: Some purists might suggest that storing part numbers twice like this is redundant data. But technically, it is not. It is RELATED data - but not redundant data. The line items in one case are from your RFQ. In the other case, from a quote. You could use the RFQ line items to populate an entry for each quote, but they ARE different in origin and depend on different keys.

 

[Steelman]

I really don't care about line item cost per se. I may have lead you to believe this and this is my fault. What I care about is what you described further into your most recent reply. I am interested in knowing what each supplier has quoted for each line item. I envision in the beginning entering the suppliers and line items into a form. Using the form information to create a printed quote report that will go out to each of the suppliers for the RFQ number and then send to the respective suppliers. When I receive the quote back I would open back up the form I used to enter the original data and populate the price for each item from each supplier. I would expect that I would create detailed reports later to evaluate the data in a number of ways.


Thanks!!