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" wrote:
> 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 Gombart wrote:
> > 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
> >
> >
> .
>
|
Similar Threads
