Nightmare

C

Candi Cain

Help!

I have a form (Quote form). I have two vendors bidding on one job. My form
has two columns--one for each vendor. The fields in the table are named

Vendor A-Sale Price
Vendor B-Sale Price
Vendor A-Shipping
Vendor B-Shipping

etc...

The last row in the form is titled awarded part and there is a checkbox in
each column. Once a vendor is selected, we check the box in that column...I
then want a report snapshot(already have it designed) to be generated
containing that data.

I am having trouble sorting out how to get only the fields from the winning
bidder to appear on the snapshot, without having two separate queries set up.
So, currently, the user has to select Vendor A-quote or Vendor B-quote.
What I would like is for the end user to select "Send quote to customer" and
have the report generated by entering the quote # and being populated with
the information from the winning vendor...

Help?
Thanks!
Candi Cain
 
A

Arvin Meyer [MVP]

Your problem stems from your design. You need 4 fields:

VendorID Long Integer
SalePrice Currency
Shipping Currency
Awarded Yes/No

Other fields such as BidID (autonumber PK) and BidDate might also be useful.

A crosstab query will display the required information in your quote form or
report. You can use a combo box to choose the Vendor who gets awarded the
bid, and an Update query will check each row.

Update MyTable Set Awarded = True Where VendorID = Forms!MyForm!MyCombo And
BidDate = #7/29/08#;
 
C

Candi Cain

Not sure I follow...i am not familiar with crosstab queries, or SQL..

here are the fields from my table:
Quote #
Customer
Date
Slagel Quote Expiration Date
Part Number
Pieces Quoted
Sheet Size
Material Spec
Sq Feet per Sheet
Total Lbs
Slagel Delivered Sheet Cost
DTech Delivered Sheet Cost
Slagel Nesting
DTech Nesting
Slagel Material Cost per Piece
DTech Material Cost per Piece
Slagel Material Markup
DTech Material Markup
Slagel Laser Cut Cost
DTech Laser Cut Cost
Slagel Heat Treat Cost
DTech Heat Treat Cost
Slagel Heat Treat Surcharge
DTech Heat Treat Surcharge
Slagel Freight Charge
DTech Freight Charge
Slagel Packaging Material
DTech Packaging Material
Slagel Buffer
DTech Buffer
Slagel Sale Price
DTech Sale Price
Slagel Awarded Part
DTech Awarded Part
Notes

When a quote is ready to be sent to a customer, I am wanting to select the
following to be put in a report:

Date
Quote #
Part Number
Pieces Quoted
Material Spec
Sale Price

Based on the "awarded part" field. When the user enters a quote number, I
would like the report to produce the above information from only the vendor
awarded the job...
 
A

Arvin Meyer [MVP]

Apparently you have 2 Vendors Slagel and DTech. What happens when you get
another? Or 2 or 3 more? Your design falls flat on its face. What you have
done is to try to use Access to build a spreadsheet. That never works. Based
on what I see, these are the tables and fields you need:

tblVendor
VendorID Autonumber Primary Key
VendorName Text
VendorAddress Text
etc.

tblCustomer
CustomerID Autonumber Primary Key
CustomerName Text
CustomerAddress Text
etc.

tblMaterial
PartNumber Autonumber Primary Key
Sheet Size
MaterialSpec
Sq Feet per Sheet
TotalLbs
DeliveredSheetCost
Nesting
MaterialCostPerPiece
MaterialMarkup
LaserCutCost
HeatTreatCost
HeatTreatSurcharge
FreightCharge
PackagingMaterial
Buffer

tblBid
BidID Autonumber Primary Key
VendorID Long Integer Foreign Key
PartNumber Long Integer Foreign Key
DeliveredCost Currency
Margin Double
Selling Price Currency
Awarded Yes/No

tblQuotes
QuoteID Primary Key
CustomerID Foreign Key
Date
QuoteExpirationDate
PartNumber Foreign Key
PiecesQuoted

I may have missed something, but that's roughly how you would build a
database to do what you want. If all this seems completely foreign to you,
I'd recommend that you have a look at some database design principles. First
have a look at:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

then try:

http://www.accessmvp.com/Strive4Peace/Index.htm

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
A

Arvin Meyer [MVP]

To answer both of your questions (displaying both vendors side-by-side, and
the field list)

1. Building a query to isolate each vendor, then using 2 subform's (1 for
each) will solve your display problem. If you expect multiple differing
vendors over a period of time, you can code the subforms' recordsources to
use different queries, or even build the queries in code, dynamically. I
suspect manually changing the recordsource will work for the time being,
until you develop the skills to write the code.

2. I really didn't know which fields were necessary, so I just grabbed what
you had in your posting. You have a choice in how you design this.:

a. You can pick only the fields which are common to every product.
b. You can add, and leave unfilled, fields which are important to a
specific product.
c. You can build 1 or more tables which contain the extra fields, and
use them on a 1 to 1 relationship.

I prefer the first choice since you can't really compare data when only 1 of
the vendors has it and the other doesn't. If you really need to store the
extra data, and you will be filling it in at least 25% of the time, use the
second choice (b.). The 3rd choice (c.) is more complex and really only
necessary if there's only a few products which differ.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
Top