S
Scott Sabo
Hello-
I had never written a query to get two databases to share info and was
sucessful in doing so, but now I see I have a problem. I have a
corporate database called Price Book which houses various information
on products and pricing. I built a seperate database which will be
used to generate quotes and bids on the products housed in the Price
Book database. Note: I could not just add the quote building features
in the Price Book database as it is owned by my wifes company and I
don't have permission to modify it.
I built the database called Quote Builder and was able to create a set
of queries to pull information from the Price Book DB into the Quote
DB which are then displayed on a form. Through this form, users can
select items that they want added to a quote report, select quantities
needed, select from a range of markup percentages, etc. The problem
that I have is that I could not find a way to add new lines to the
form, say if a user needed to quote an item that was not part of the
Price Book DB (a special order product from an outside vendor). If I
overwrite the data on one of the existing products, the queries write
that info back to the Price Book DB, a big no-no for me. I want to add
a button to the bottom of the form called "Add Special Order Item"
which will bring the user to a new line on the form. They will type in
the info into the blank existing fields: Item Description, Item
number, Vendor, Cost, etc. How do I prevent the queries from writing
back to the Price Book DB? In effect, I only want a one-way dialog
between the DB's. I want Quote Builder to receive info from Price Book
but NOT vice verca. Any ideas? Below are the queries in Quote Builder
which allow the import of info from Price Book to Quote Builder:
qryEffectiveCostUpdate
SELECT ItemPriceTolerance.EffectiveCost, ItemPriceTolerance.ItemNo
FROM ItemPriceTolerance IN 'c:\documents and
settings\sabosis\desktop\electronic price book.mdb';
qryProdDataUpdate
SELECT ProductsData.ItemNo, ProductsData.ItemDesc,
ProductsData.Vendor, ProductsData.[Case Quantity]
FROM ProductsData IN 'c:\documents and
settings\sabosis\desktop\electronic price book.mdb';
These two queries are then combined into the following main query:
qryEPBUpdate
SELECT qryProdDataUpdate.ItemNo, qryProdDataUpdate.ItemDesc,
qryProdDataUpdate.Vendor, qryProdDataUpdate.[Case Quantity],
tblItems.QuantityNeeded, qryEffCostUpdate.EffectiveCost,
tblItems.QuoteThis, tblItems.Margin,
[margin]*[EffectiveCost]+[EffectiveCost] AS [Price Each], [Price
Each]*[QuantityNeeded] AS [Ext Price]
FROM (qryProdDataUpdate INNER JOIN qryEffCostUpdate ON
qryProdDataUpdate.ItemNo = qryEffCostUpdate.ItemNo) INNER JOIN
tblItems ON qryProdDataUpdate.ItemNo = tblItems.ItemNo
ORDER BY qryProdDataUpdate.ItemDesc;
I hope this info gives a framework of what I am trying to accomplish.
The quotes are beautiful, I just need a way to add items outside of
the PriceBook to the last query "qryEPBUpdate" so that when the report
is run it will show them along with the other items selected on the
main quote form.
Thanks in advance-
Scott
I had never written a query to get two databases to share info and was
sucessful in doing so, but now I see I have a problem. I have a
corporate database called Price Book which houses various information
on products and pricing. I built a seperate database which will be
used to generate quotes and bids on the products housed in the Price
Book database. Note: I could not just add the quote building features
in the Price Book database as it is owned by my wifes company and I
don't have permission to modify it.
I built the database called Quote Builder and was able to create a set
of queries to pull information from the Price Book DB into the Quote
DB which are then displayed on a form. Through this form, users can
select items that they want added to a quote report, select quantities
needed, select from a range of markup percentages, etc. The problem
that I have is that I could not find a way to add new lines to the
form, say if a user needed to quote an item that was not part of the
Price Book DB (a special order product from an outside vendor). If I
overwrite the data on one of the existing products, the queries write
that info back to the Price Book DB, a big no-no for me. I want to add
a button to the bottom of the form called "Add Special Order Item"
which will bring the user to a new line on the form. They will type in
the info into the blank existing fields: Item Description, Item
number, Vendor, Cost, etc. How do I prevent the queries from writing
back to the Price Book DB? In effect, I only want a one-way dialog
between the DB's. I want Quote Builder to receive info from Price Book
but NOT vice verca. Any ideas? Below are the queries in Quote Builder
which allow the import of info from Price Book to Quote Builder:
qryEffectiveCostUpdate
SELECT ItemPriceTolerance.EffectiveCost, ItemPriceTolerance.ItemNo
FROM ItemPriceTolerance IN 'c:\documents and
settings\sabosis\desktop\electronic price book.mdb';
qryProdDataUpdate
SELECT ProductsData.ItemNo, ProductsData.ItemDesc,
ProductsData.Vendor, ProductsData.[Case Quantity]
FROM ProductsData IN 'c:\documents and
settings\sabosis\desktop\electronic price book.mdb';
These two queries are then combined into the following main query:
qryEPBUpdate
SELECT qryProdDataUpdate.ItemNo, qryProdDataUpdate.ItemDesc,
qryProdDataUpdate.Vendor, qryProdDataUpdate.[Case Quantity],
tblItems.QuantityNeeded, qryEffCostUpdate.EffectiveCost,
tblItems.QuoteThis, tblItems.Margin,
[margin]*[EffectiveCost]+[EffectiveCost] AS [Price Each], [Price
Each]*[QuantityNeeded] AS [Ext Price]
FROM (qryProdDataUpdate INNER JOIN qryEffCostUpdate ON
qryProdDataUpdate.ItemNo = qryEffCostUpdate.ItemNo) INNER JOIN
tblItems ON qryProdDataUpdate.ItemNo = tblItems.ItemNo
ORDER BY qryProdDataUpdate.ItemDesc;
I hope this info gives a framework of what I am trying to accomplish.
The quotes are beautiful, I just need a way to add items outside of
the PriceBook to the last query "qryEPBUpdate" so that when the report
is run it will show them along with the other items selected on the
main quote form.
Thanks in advance-
Scott