Create query from record in table in Access VBA

Question

I'm using Microsoft Access for product pricing for a small business. I have a products table, our supplier's prices, and specialized customers table. There are different queries that take our supplier's price and apply a mark up to different price levels. For some customers we give the a special price on certain products, but the rest of the products they belong to a certain price level. I then combine a customer's special pricing with their price level query. If a special price exists it uses that price instead of their regular price level price. To get this query I first have to make a query for each customer to get all of their products and special prices out of the customer pricing query. Then I join that query with the price level query. If I don't do the first step and join customer pricing w/ price level query, customers that have special pricing on the same product, that product shows up multiple times which I can't have. So if I create a query to take their prices out of customer pricing to begin with and then join with price level it works.

Sorry if I'm not clear. If you have question or want more detail how this is set up I will provide.

I don't know VBA at all. I can read it and follow, but never written VBA code.

What I want is VBA code to search the customers table and check if each customer has its own query that separates their prices from customer prices, so if a new customer is added it automatically creates a query for them. Then create a query that joins their price level with the query that was just created.

Is this possible. Or is there any better way to go about this. Thanks much.

Here's my tables so far

Supplier         Products         CustomerPrds       Customers
Prcode <--------Prcode 1------many Prcode
Prname                                    CustID many-------1 CustID
                                                CustPrice

Here's an example of queries for one price level and one customer

J6
Field: PrCode PrName PrBulked PrMultiple $/GAL $/UNIT
Src: All of these are from Products tbl "$/GAL:calculated field to mark up supplier$" "$/UNIT:$/GAL * PrMultiple"
CustomerPricing
Field: CustID CustName PrCode PrName Cust$
Src: CustPrds Custms CustPrds Prds CustPrds
Cust1
All fields from CustPricing query where CustID=1
Joined Cust1 and J6 Join all records from J6 and records where J6.PrCode=CustomerPricing.PrCode
PrCode - Multiple from J6. $/Gal:if CustID=1, cust$, J6.$/GAL

Here is the SQL for the queries

SELECT [Customer Products].customerid,
       customers.customer,
       [Customer Products].[Product Number],
       chevron_products.[MATERIAL NAME],
       chevron_products.bulked,
       chevron_products.uom,
       chevron_products.multiple,
       [Customer Products].[Customer Price],
       [Customer Price] * [Chevron_Products]![Multiple] AS [$/UNIT]
FROM   customers
       INNER JOIN ((chevron91311
                    RIGHT JOIN chevron_products
                      ON chevron91311.[MATERIAL NUMBER] =
                         chevron_products.[MATERIAL NUMBER])
                   INNER JOIN [Customer Products]
                     ON chevron_products.[MATERIAL NUMBER] =
                        [Customer Products].[Product Number])
         ON customers.[Customer Number] = [Customer Products].customerid;

SELECT [Customer Pricing].customerid,
       [Customer Pricing].[Product Number],
       [Customer Pricing].[Customer Price]
FROM   [Customer Pricing]
WHERE  (( ( [Customer Pricing].customerid ) = 2 ));

SELECT j6.[MATERIAL NUMBER],
       j6.[MATERIAL NAME],
       j6.bulked,
       j6.uom,
       j6.multiple,
       Iif([Customer Pricing].[CustomerID] = 2,
       [Customer Pricing].[Customer Price], [J6].[$/GAL]) AS [$/GAL],
       [$/GAL] * [J6].[Multiple]
       AS [$/UNIT]
FROM   j6
       LEFT JOIN cobbprds
         ON j6.[MATERIAL NUMBER] = cobbprds.[Product Number]
ORDER  BY j6.[MATERIAL NAME];

Hey Lars, the best way to post queries on Stackoverflow is to switch to SQL view in Access and cut and paste the text from that window. It looks something like SELECT stuff FROM Table1 ... — Fionnuala
At the very least, you can refer to the customer as a control on a form, for example: [Customer Pricing].customerid = Forms!MyForm!cboSelectCustomer This would mean that you did not need a query for each customer. There are other things that can be simplified. — Fionnuala
I haven't really worked with forms before. So would I create a form from Customer Pricing or Customers? — Lars Hovden

Richard Richard · Accepted Answer · 2012-06-06T15:19:16

Not completely sure I understand, but it looks like a quick VBA conditional statement would suffice... you can use dlookup to determine if there's a customer specific price and set your query based on the result. If I'm way off base - can you provide a more detailed scenario with sample data?

Here's some VBA code I would use to choose between two different queries based on the presence of a CustPrice for that customer/product...

if not isnull(dlookup("CustPrice","CustomerPrds","CustID=" & Forms!MyForm!cboSelectCustomer & " AND Prcode=" & Forms!MyForm!cboSelectProduct)) then
  lookupQuery = "SELECT foo FROM bar WHERE baz='abc'"
else
  lookupQuery = "SELECT foo From bar WHERE baz='xyz'"

Now you've conditionally set the query to do what you need it to and can execute through a recordset:

dim rs as recordset
set rs = currentdb.openrecordset(lookupQuery)
while not rs.eof
  'do stuff with the recordset here
wend
rs.close

By the way - the dlookup above is the same as this query. While I'm sure there are overhead differences between using a dlookup and a recordset, I'm not familiar with what they are.

qry = "SELECT CustPrice FROM CustomerPrds WHERE CustID=" & Forms!MyForm!cboSelectCustomer & " AND Prcode=" & Forms!MyForm!cboSelectProduct

Create query from record in table in Access VBA

1 Answers