Nightmare

[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

 

[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#;

 

[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...

 

[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

 

[Candi Cain]

The tables and fields make sense; however, how do I tie them into a form?
The user needs to be able to enter quotes from multiple vendors on one form,
so they can see the figures side by side.

The following fields are the same, regardless of vendor:
Quote # (auto number)
Date
Customer Name
Contact Name (determined by Customer Name)
Contact Number (determined by Customer Name)
Part Number
Pieces Quoted
Quote Expiration Date
Material Spec
Sheet Size (determined by Material spec)
Total lbs (Determined by Material Spec)

The remaining fields(listed below) vary by vendor:
Delivered Sheet Cost
Nesting
Material Cost/piece
Material Markup
Laser cut cost
heat treat cost
etc....

So my set up is one quote/customer for many vendors.

As I mentioned in my original post--I was very fluent in Access, but appear
to have gotten a tad rusty while out of the loop.

Your assistance is greatly appreciated.

 

[Candi Cain]

The fields you have listed in the tblMaterial, change depending on the
vendor. The constants for the material are the part number, the sheet size,
the material spec, the sqfeetpersheet and the total lbs...the remaining
fields you have listed all change depending on the vendor....

 

[Larry Linson]

The remaining fields(listed below) vary by vendor:
Delivered Sheet Cost
Nesting
Material Cost/piece
Material Markup
Laser cut cost
heat treat cost
etc....

It has been a little difficult for me to follow your description. For
example, in the quoted text above, are you implying that the Fields noted
"vary by vendor" in _definition_ or that the content of those fields varies.

If the former, then you have a problem because fields of the same
identification, should also be of the same definition, in a relational
database table. Using some "implicit" difference in definition based on
aother factor (in your case, vendor) is a "convention" that will, sooner or
later, probably sooner, rise up to bite you as you continue to work with the
DB.

The point that Arvin has made is that what you are doing is appropriate for
a spreadsheet, but Access is not, and never was intended to be nor
advertised as, "Excel spreadsheet on Steroids". You have to take a database
view, not a spreadsheet view, if you are going to make effective use of a
database. If you insist on using the database as a giant spreadsheet, you
should create your application with a giant spreadsheet (Excel 2007 vastly
increased the limits), and not try to force the database to BE a giant
spreadsheet.

Frankly, I've seen nothing so far that would necessarily prevent your
creating a user-friendly, solid, stable database application for your needs.

Larry Linson
Microsoft Office Access MVP






in message news:[email protected]...

 

[Candi Cain]

thanks, Larry.

The fields vary in content, not definition...I have created the tables
suggested by Arvin and am trying to move forward with building my database.
One of the big hurdles I am having is the auto populate feature. For
Example, when a user selects a customer on a quote form, I would like all
information related to that information to populate....

 

[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