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

  • Thread starter Thread starter dan dungan
  • Start date Start date
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
 
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...)
 
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.
 
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
 
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
 
Thanks Tina,

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

Thanks for sending that link.

Dan
 
Back
Top