How to normalize tables in a sales quote db with multiple quantitiesfor each part number

D

dan dungan

Hi Access designers:

Using Excel 2000 and Access 2000 on windows 2000 professional, I want
to produce a report
in Access that shows how many quotes and how many items quoted by date
for each agent with the following layout:

----------|-------------------|------------------|----------------------|
| Agent1 | Agent2 | Totals |
----------|-------------------|------------------|----------------------|
|Quote | Item |Quote | Item | Quote | Item |
Date |Qty | Qty |Qty | Qty | Qty | Qty |
----------|----------|--------|---------|--------|------------|----------|
3/1/09 | 10 | 45 | 9 | 30 | 19 | 75 |
3/2/09 |14 | 27 | 8 |14 | 41 | 41 |
----------|----------|--------|---------|--------|------------|----------|
Total | 24 | 72 | 17 | 44 | 41 | 116 |
----------|----------|--------|---------|--------|-----------|-----------|
avg items | | | | | |
per quote | 3 | | 2.5 | | 2.8 |
----------|----------|--------|---------|--------|-----------|-----------|
avg per | | | | | |
day | 12 | 36 | 8.5 | 22 | 20.5 | 58 |
-----------------------------------------------------------------------|

I use Excel to calculate the quote and then save the pricing details
in a text file to
import into Access to create the quote report that we send to the
customer.

The data come from a table in the following format:

Field Data
-------------------------------------------------------------
id 124
PartNum GA800NB16-12C1
Quantity 1
UnitPrice $96.85
Delivery 7 Weeks
CompetitorPartNum H447HS001N2020
Date 10/23/2007
Time 9:14:00 AM
Customer HARVARD CUSTOM
CustRefNum H009
EAIRep Molly Brown
QuoteNum 2
ItemNumber 1
ImportDate 10/23/2007
ImportTime 9:14:00 AM

There can be up to 6 quantities quoted for each part number.
My report now counts all the instances of a part number.
I've been using a crosstab query based on a totals query to get
the summary, but the report is not accurate and access
returns an error message that the query is too complex.

I want to move the entire process to Access, and I'm developing a
table schema for the
migration.

I know the data needs to be normalized, but I'm getting
confused about what fields need to be in the tblQuoteDetails
or if I need a partNumber table or a quantities table.

Below are proposed table designs. I don't know how to proceed.

I appreciate any suggestions.

Thanks,

Dan
===================================================
Proposed Table Designs

---------------------------------------------------------------
tblCustomer
CId 1
CCompName HARVARD CUSTOM

---------------------------------------------------------------
tblEmployee
EmployeeId 1
FirstName Molly
LastName Brown
---------------------------------------------------------------
tblQuote
QuoteNum 124
CId 1
EmployeeID 1
CRefNum H009
Date 3/26/2009
Time 8:58 AM
 
P

Piet Linden

Hi Access designers:

Using Excel 2000 and Access 2000 on windows 2000 professional, I want
to produce a report
in Access that shows how many quotes and how many items quoted by date
for each agent with the following layout:

----------|-------------------|------------------|----------------------|
          |    Agent1     |  Agent2     | Totals             |
----------|-------------------|------------------|----------------------|
          |Quote | Item |Quote | Item | Quote | Item   |
Date   |Qty     | Qty  |Qty    | Qty  | Qty     | Qty    |
----------|----------|--------|---------|--------|------------|----------|
3/1/09 | 10     | 45    | 9      | 30    | 19       | 75      |
3/2/09 |14      | 27    | 8      |14     | 41      | 41      |
----------|----------|--------|---------|--------|------------|----------|
Total   |  24     | 72   | 17     | 44    | 41      | 116    |
----------|----------|--------|---------|--------|-----------|-----------|
avg items       |        |         |        |           |           |
per quote       | 3     |         |  2.5  |          |  2.8    |
----------|----------|--------|---------|--------|-----------|-----------|
avg per          |        |         |        |            |          |
day     | 12     | 36   | 8.5    |  22  | 20.5     |  58     |
-----------------------------------------------------------------------|

I use Excel to calculate the quote and then save the pricing details
in a text file to
import into Access to create the quote report that we send to the
customer.

The data come from a table in the following format:

Field                           Data
-------------------------------------------------------------
id                              124
PartNum                 GA800NB16-12C1
Quantity                        1
UnitPrice                       $96.85
Delivery                        7 Weeks
CompetitorPartNum       H447HS001N2020
Date                            10/23/2007
Time                             9:14:00 AM
Customer                        HARVARD CUSTOM
CustRefNum              H009
EAIRep                  Molly Brown
QuoteNum                        2
ItemNumber              1
ImportDate              10/23/2007
ImportTime              9:14:00 AM

There can be up to 6 quantities quoted for each part number.
My report now counts all the instances of a part number.
I've been using a crosstab query based on a totals query to get
the summary, but the report is not accurate and access
returns an error message that the query is too complex.

I want to move the entire process to Access, and I'm developing a
table schema for the
migration.

I know the data needs to be normalized, but I'm getting
confused about what fields need to be in the tblQuoteDetails
or if I need a partNumber table or a quantities table.

Below are proposed table designs. I don't know how to proceed.

I appreciate any suggestions.

Thanks,

Dan
===================================================
Proposed Table Designs

---------------------------------------------------------------
tblCustomer
CId                             1
CCompName               HARVARD CUSTOM

---------------------------------------------------------------
tblEmployee
EmployeeId              1
FirstName                       Molly
LastName                        Brown
---------------------------------------------------------------
tblQuote
QuoteNum                        124
CId                             1
EmployeeID              1
CRefNum                 H009
Date                            3/26/2009
Time                            8:58 AM
Click to expand...

QuoteDetails are like Line Items or Invoice Line Items...
(QuoteNum (link back to tblQuote), PartNum (link to Parts table),
Quantity, UnitPrice...)
 
F

Fred

Adding to Piet's good advice

Start by thinking about the entities that you want to database. Your table
structure is your foundation.....getting that right / wrong that will make
everything else work or not work

Here's my first guess: I'm making up names only for communication..... of
course shorten them or use your own names

(I'm assuming employee = agent in the context of this DB)

Certainly you'll need these tables:

Quotes (PK = QuoteID, include FK AgentID) )
QuoteItems (PK = QuoteID, include FK QuoteID)
Agents (PK = AgentID)

Depending on whether or each of the following is a "list" that you
repetitively draw from, the following would also probably be tables:

Customers (PK = CustomerID)
Parts (PK = PartNum)

Customer is a field in your Quotes table. If you create the Customers
table, it's a FK CustomerID, otherwise it's just typed in text. Ditto for
parts in the Quote items table.


If you do create a "customers" table, you now have to decide which of the
following connections is most solid: agent-to-quote or agent-to-customer.
Put a FK AgentID field in your quote table or customers table accordingly.


If you don't create a "customer's table, there's no question....the agent
link is to your quotes table.


Then link all fields to their namesakes in other tables.
 
D

dan dungan

Hi Piet,

Thanks for your help. I've been considering your feedback.

Thanks,

Dan
 
D

dan dungan

Hi Fred,

I've been considering your feedback.
Quotes (PK = QuoteID, include FK  AgentID) )
QuoteItems  (PK = QuoteID, include FK QuoteID)
Agents  (PK = AgentID)  
Click to expand...

I'm not clear about QuoteItems. It seems QuoteID is both a PK and FK.

Please talk more about how that works.

Thanks,

Dan
 
T

tina

i think perhaps Fred meant to say

QuoteItems (PK = QuoteItemID, include FK QuoteID)

though even if you use the same name for the primary key fields of two
different tables, they're not interchangeable. in the original posted text
QuoteItems (PK = QuoteID, include FK QuoteID)
Click to expand...

the "PK = QuoteID" refers to the primary key field of table QuoteItems, and
the "FK QuoteID" refers to the primary key field of table Quotes which is
used as a foreign key in table QuoteItems.

if the relationships, and/or primary key / foreign key references, aren't
clear, then recommend you read up/more on relational design principles. for
more information, see
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

hth


Hi Fred,

I've been considering your feedback.
Quotes (PK = QuoteID, include FK AgentID) )
QuoteItems (PK = QuoteID, include FK QuoteID)
Agents (PK = AgentID)
Click to expand...

I'm not clear about QuoteItems. It seems QuoteID is both a PK and FK.

Please talk more about how that works.

Thanks,

Dan
 
D

dan dungan

Thanks Tina,

I appreciate your comments, as well. I have returned to reading about
relational design principles.

Thanks for sending that link.

Dan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple line items in quote 2

Top