Creating a select query using sql in VBA

  • Thread starter Thread starter Les Gombart
  • Start date Start date
L

Les Gombart

I have one table called PRICING and 21 other tables that I link separately to
this table to create a SELECT query. In other words, instead of creating 21
SELECT queries, I want to be able to use variables within VBA to change the
table name that links to the pricing table (fileds within all 21 other tables
are named exactly the same).

I have been trying to create a VBA procedure to run the SQL statement to
temporarily create the SELECT query.

I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get
the SQL statement right.

When I copy and paste the SQL statment from an exisiting query, I still get
errors.

The SQL statement that will eventually replace with variables is as follows:

SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base,
natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %],
([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;

When I try "clean up" the code to:
strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material,
pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP
%], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf
& _
"FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"

I am still not successful... HELP PLEASE
 
Try this:

strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material,
pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP
%], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & _
"FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy"

I got rid of the vbcrlf and replaced it by " & _

The underscore at the end is the linebreaker for statements you want to
break down.

hth
 
First, you can NOT use DoCmd.RunSQL to execute a SELECT query. You can only
use DoCmd.RunSQL to execute ACTION (and DDL) queries. Action queries are
queries that add records, delete records, or update records.

If the 21 other tables all have the same structure and field names, then you
probably have a design problem. You should have one table with the current
structure and one additional field that identifies whatever the current reason
is for having 21 identical tables.

A simple trick to build the SQL string would be to use a table alias. I'll
assume that NatCat is the table you want to vary.

Dim strSQL as String
Dim strAuxTable as String 'set strAuxTable = to the name of the changing table

strSQL = "SELECT p.Hierarchy AS base_Hierarchy" & _
", p.Material, p.Base, n.[Trade %]" & _
", (([Base]*[Trade %])) AS Net1, n.[EDLP %]" & _
", ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net" & _
" FROM pricing As P INNER JOIN [" & strAuxTable & "] As N" & _
" ON p.Hierarchy = p.hierarchy"

Your next step is to use the string as a record source for a form or report;
or as a row source in a listbox or combobox;
or as the SQL property of a query;
or as the source for a recordset. Whatever you do depends on what you are
trying to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Les said:
I have one table called PRICING and 21 other tables that I link separately to
this table to create a SELECT query. In other words, instead of creating 21
SELECT queries, I want to be able to use variables within VBA to change the
table name that links to the pricing table (fileds within all 21 other tables
are named exactly the same).

I have been trying to create a VBA procedure to run the SQL statement to
temporarily create the SELECT query.

I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get
the SQL statement right.

When I copy and paste the SQL statment from an exisiting query, I still get
errors.

The SQL statement that will eventually replace with variables is as follows:

SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base,
natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %],
([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;

When I try "clean up" the code to:
strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material,
pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP
%], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf
& _
"FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"

I am still not successful... HELP PLEASE
Click to expand...
 
Thanks Maurice... I managed to download a whole function statement and VBA
procedure that solved my problem.

Thanks anyway

Maurice said:
Try this:

strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material,
pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP
%], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & _
"FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy"

I got rid of the vbcrlf and replaced it by " & _

The underscore at the end is the linebreaker for statements you want to
break down.

hth
--
Maurice Ausum


Les Gombart said:
I have one table called PRICING and 21 other tables that I link separately to
this table to create a SELECT query. In other words, instead of creating 21
SELECT queries, I want to be able to use variables within VBA to change the
table name that links to the pricing table (fileds within all 21 other tables
are named exactly the same).

I have been trying to create a VBA procedure to run the SQL statement to
temporarily create the SELECT query.

I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get
the SQL statement right.

When I copy and paste the SQL statment from an exisiting query, I still get
errors.

The SQL statement that will eventually replace with variables is as follows:

SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base,
natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %],
([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;

When I try "clean up" the code to:
strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material,
pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP
%], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf
& _
"FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"

I am still not successful... HELP PLEASE
Click to expand...
Click to expand...
 
Thanks John... learnt the hard way about DoCmd.RunSQL

John Spencer said:
First, you can NOT use DoCmd.RunSQL to execute a SELECT query. You can only
use DoCmd.RunSQL to execute ACTION (and DDL) queries. Action queries are
queries that add records, delete records, or update records.

If the 21 other tables all have the same structure and field names, then you
probably have a design problem. You should have one table with the current
structure and one additional field that identifies whatever the current reason
is for having 21 identical tables.

A simple trick to build the SQL string would be to use a table alias. I'll
assume that NatCat is the table you want to vary.

Dim strSQL as String
Dim strAuxTable as String 'set strAuxTable = to the name of the changing table

strSQL = "SELECT p.Hierarchy AS base_Hierarchy" & _
", p.Material, p.Base, n.[Trade %]" & _
", (([Base]*[Trade %])) AS Net1, n.[EDLP %]" & _
", ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net" & _
" FROM pricing As P INNER JOIN [" & strAuxTable & "] As N" & _
" ON p.Hierarchy = p.hierarchy"

Your next step is to use the string as a record source for a form or report;
or as a row source in a listbox or combobox;
or as the SQL property of a query;
or as the source for a recordset. Whatever you do depends on what you are
trying to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Les said:
I have one table called PRICING and 21 other tables that I link separately to
this table to create a SELECT query. In other words, instead of creating 21
SELECT queries, I want to be able to use variables within VBA to change the
table name that links to the pricing table (fileds within all 21 other tables
are named exactly the same).

I have been trying to create a VBA procedure to run the SQL statement to
temporarily create the SELECT query.

I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get
the SQL statement right.

When I copy and paste the SQL statment from an exisiting query, I still get
errors.

The SQL statement that will eventually replace with variables is as follows:

SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base,
natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %],
([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;

When I try "clean up" the code to:
strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material,
pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP
%], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf
& _
"FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"

I am still not successful... HELP PLEASE
Click to expand...
.
Click to expand...
 
Hi John...'nother question.

I am not too sure about your statement about the design problem. If you
don't mind I would like to explain my thought process and maybe you could
suggest a better way?

The first table is a list of product types and their basic prices (table is
called pricing and key field that links to other tables is hierarchy).

I the have a host of customers that are linked to a discount structure
dependant on what type of customer they are.

So for example I may have a customer that is a restuarant and therefore is
linked to the restaurant discount structures. Hence the 21 other tables(21
different discount structures)

When I want to create a promotional document that "calculates" pricing for
the relevant customer and then allows an additional promotional discount, the
selection of the qualifying customer will update a variable with the
discounting table to then allow a select query to update a form control...

Hope I make sense

John Spencer said:
First, you can NOT use DoCmd.RunSQL to execute a SELECT query. You can only
use DoCmd.RunSQL to execute ACTION (and DDL) queries. Action queries are
queries that add records, delete records, or update records.

If the 21 other tables all have the same structure and field names, then you
probably have a design problem. You should have one table with the current
structure and one additional field that identifies whatever the current reason
is for having 21 identical tables.

A simple trick to build the SQL string would be to use a table alias. I'll
assume that NatCat is the table you want to vary.

Dim strSQL as String
Dim strAuxTable as String 'set strAuxTable = to the name of the changing table

strSQL = "SELECT p.Hierarchy AS base_Hierarchy" & _
", p.Material, p.Base, n.[Trade %]" & _
", (([Base]*[Trade %])) AS Net1, n.[EDLP %]" & _
", ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net" & _
" FROM pricing As P INNER JOIN [" & strAuxTable & "] As N" & _
" ON p.Hierarchy = p.hierarchy"

Your next step is to use the string as a record source for a form or report;
or as a row source in a listbox or combobox;
or as the SQL property of a query;
or as the source for a recordset. Whatever you do depends on what you are
trying to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Les said:
I have one table called PRICING and 21 other tables that I link separately to
this table to create a SELECT query. In other words, instead of creating 21
SELECT queries, I want to be able to use variables within VBA to change the
table name that links to the pricing table (fileds within all 21 other tables
are named exactly the same).

I have been trying to create a VBA procedure to run the SQL statement to
temporarily create the SELECT query.

I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get
the SQL statement right.

When I copy and paste the SQL statment from an exisiting query, I still get
errors.

The SQL statement that will eventually replace with variables is as follows:

SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base,
natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %],
([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;

When I try "clean up" the code to:
strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material,
pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP
%], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf
& _
"FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"

I am still not successful... HELP PLEASE
Click to expand...
.
Click to expand...
 
You know your data and your requirements. HOWEVER, if your 21 discount tables
have the same structure then I would suspect that they can be combined into
one table with a new field named BusinessType or DiscountType.

Then when you want discount information for a restaurant you filter the result
by specifying the DiscountType = "restaurant". A BIG advantage of this
approach is that if you get a new type of business, you don't need to add a
NEW table and change all your forms, reports, code, etc. to work with this new
business type. You just add the needed records to the table and everything
should work.

If your discount tables vary in structure, then you probably have a valid
argument for maintaining the different tables.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top