Using the information inputted in a table as a table name

[kella101]

Hi,

I was wondering if anyone could tell me the correct syntax, or even if its possible to do the following, please remember that this code wil be written within the VB code... not in a query...

sqlstring = " "
sqlstring = sqlstring & " INSERT INTO markup SELECT TOP 1 Customername AS [Cust_Name],"
sqlstring = sqlstring & " customers.address AS [Cust_Address],"
sqlstring = sqlstring & " customers.postcode AS [Cust_Postcode],"
sqlstring = sqlstring & " customers.active AS [Active_Customer],"
sqlstring = sqlstring & " iif(Active_Customer =1, 'activecustomer','inactivecustomer) AS [Cust_Current],"
sqlstring = sqlstring & " FROM customers"
sqlstring = sqlstring & " WHERE customer.address = " & "'" & Inputaddress & "'"
sqlstring = sqlstring & " AND Cust_Current.address = " & "'" & Inputaddress & "'"
sqlstring = sqlstring & ";"

Therefore, using the value of what "Cust_Current" is as the table name in the where clause. So the where clause for the above statement would either be processed as ;

sqlstring = sqlstring & " AND activecustomer.address = " & "'" & Inputaddress & "'"
or
sqlstring = sqlstring & " AND inactivecustomer.address = " & "'" & Inputaddress & "'"

Depending on what the value of Active_Customer is.

Can anyone help?

 

[georgedwilkinson]

You cannot do what the above code tries to do.

Do you actually have two tables, one called "activecustomer" and one called "inactivecustomer"? If so, you have a poorly normalized database and need to fix it according to rules laid down by Mr. Codd so long ago.

If you don't want to fix it, you can use a union to get your data, if I understand your code correctly.

 

[kella101]

hi, that query was just made up off the top of my head. the actual query is far more complex than that. Also i have no control over the databases, and never will be able to change them. Also a union is out of the question because this would mean creating a union of over 150,000 records from ODBC databases. these databases are completely out of my control, and come from either our mrp system and combined with records from our other system.

All i am trying to do mainly is know how to change a line in the WHERE clause of a SQL statement, depending on a certain value. is this possible?

 

[DCrake]

Simple Software Solutions

Hi Kella

Still struggling I see. Before you attempt to run the INSERT sql you first need to determine the name of the table, correct? So first do a select sql get the table name, pass this to a variable then encode this in your INSERT sql.

Thus

Select * TOP 1 from Markup Where ....

If Active customer = 1 then
   Cust_Current = "ActiveCustomer"
Else
   Cust_Current = "InactiveCustomer"
End If

Next Encode the Insert SQL

sqlstring = sqlstring & " WHERE customer.address = " & "'" & Inputaddress & "'" 
sqlstring = sqlstring & " AND " & Cust_Current & ".address = " & "'" & Inputaddress & "'"

Code truncated for brevity

David

 

[kella101]

Hi David, yea still struggling,

The SQL i wrote in this first post was just an example, i am still using the same code i was in the previous posts you helped me with. and got it working, except for this problem with the WHERE clause.

I dont really understand the above;

"Select * TOP 1 from Markup Where ...."

How can i select all from markup before inserting anything into markup?

Also the If else statement you have wrote, this is meant for in the VB code isnt it? if so, will the VB code pick up the value of a variable set within the sql? if so what "dim" would i have to set the variable name as within vb.

I see what i should do... find the table name, set this as a variable within the vb code, then use it within my sql string as the table name. I just don't know how to do this :S

 

[DCrake]

Simple Software Solutions

Kella,

OK, first of all we will dim 2 string variables called StrTableName,StrStatus

Dim StrTableName As String
Dim StrStatus As String

Next we need to find out which table to use? Is it the Active or inactive customer.

Because the status of the customer is at customer level we can do a simple look up in the table to determine the status.

So before we attempt to do the INSERT INTO Markup SQL we need to decide the correct table name. This will appear in your code above the queryString2 section.

StrStatus = CStr(DLookup("Active_Customer","Customers","Customer_Name =' " & CustomerSelection & "'"))

Now that we know what the status is we can tell it to use the desired table. Put this code directly after the above lookup.

If StrStatus = "1"  Then
    StrTableName = "ActiveCustomer"
Else
    StrTableName = "InactiveCustomer"
End If

Then in your queryString2 SQL you need to make the following changes

....
queryString2 = queryString2 & " WHERE customer.address = " & "'" & Inputaddress & "'"
queryString2 = queryString2 & " AND " & StrTableName & ".address = " & "'" & Inputaddress & "'"
...

Hope this is a more clearer explanation for you.

David

 

[kella101]

Hi thanks i do understand that.

However, the query is a loop, and thus the customer type will be different for each result. therefore im not sure how the Dlookup will work :S

 

[DCrake]

Simple Software Solutions

Precisely

Put the code I posted immediately after your For statement. In this way customer selection will change for each pass.

David

 

[kella101]

For statement? sorry i am unfamiliar with this... i have a do until... loop

 

[kella101]

Hi the sql i wrote at the top was just an example...

But in my actual one i have the If else working, and have tested it. however to get the correct StrStatus for the If else statement, i need to use 3 different Dlookups.

StrPartgrp = (DLookup("price_group", "part", "id =' " & select_part_rst!part & "'"))

This first one finds the part_group using the part number which the loop is currently on.

StrCustName = (DLookup("JDE_Price_Group", "Custom2", "Customer_Name =' " & CustomerSelection & "'"))

This second one finds the Price group for the customer, which is inputted by the user and the field is called "CustomerSelection"

StrStatus = (DLookup("use_fixed_price", "price_factor", "customer_group =' " & StrCustName & "'AND part_group =' " & StrPartgrp & "'"))

This 3rd one uses the two previous Dlookup results. This finds a value of either 1 or 0 from the column use_fixed_price, and uses two pieces of criteria. The two pieces of criteria use the two variables from the outcomes of the previous Dlookup statements to find the correct value.

Then this value is checked via the If else statement, and then this determines which table the where clause uses.

Can you help me with the Dlookup's tho. i cant get anything out of them. it just says "Invalid use of Null"

If i can get these to work i am confident this will solve my problem for good. thanks!

 

[DCrake]

Simple Software Solutions

Kella

Without diving in at the deep end my first suggestion is to use your existing queryString2 sql and copy and paste the code to QryString2 and strip out all the fields, table and where conditions that will create a secondary query. Change the INSERT INTO to a SELECT

QryString2 = "SELECT JDE_Customer AS [JDE Customer No], " & _
                  "JDE_Price_Group AS [JDE Price Group], " & _
                  "Custom2.Customer_Name AS [Customer Name], " & _ 
                  "Part.ID AS [PartID], " & _
                  "Iif(Price_Factor.Use_fixed_price=1,Price_Factor.Fixed_Price_Factor,Price_Factor.Factor) AS [Factor] " & _ 
                  etc...
                  "FROM Custom2, Price_Factor,etc.. " & _ 
                  "WHERE PartID= '" & select_part_rst!part & "' " & _
                  "AND .... etc'"

This will give you the sql for a recordset

Dim Rs As DAO.RecordSet

Set Rs = CurrentDB.OpenRecordset(QryString2)
If Not Rs.EOF and Not Rs.BOF Then
    StrPartGroup = Rs("Price Group")
    StrCustName = Rs("Customer Name")
    StrStatus = Rs("FixedPrice")
End IF

Rs.Close
Set Rs = Nothing

Now you will be able to set the correct table name based on the info from your variables.

Did notice in your full queryString2 sql you had a line:

part.id AS Part

You also have the line

FROM part_select, part, fixed_price_list, etc

You are heading for trouble using alias field names the same as table names.

Also where is select_part_rst coming from?

David

 

[kella101]

I cant run another query like that, it will slow it down too much, this thing is searching through 120,000 parts, and another 50,000 customers etc etc... and isnt a local database. select_part_rst is the sub function which finds what part the loop is onto, thus select_part_rst!part is the current part number, this all works exactly how its meant to.

The only thing i am struggling with now is;

StrPartgrp = Nz(DLookup("price_group", "part", "id =' " & select_part_rst!part & "'"))

StrCustName = Nz(DLookup("JDE_Price_Group", "Custom2", "Customer_Name =' " & CustomerSelection & "'"))

StrStatus = Nz(DLookup("use_fixed_price", "price_factor", "customer_group =' " & StrCustName & "'AND part_group =' " & StrPartgrp & "'"))

I cant get ;

If StrStatus = 0 Then
StrTableName = "price_list"
Else
StrTableName = "fixed_price_list"
End If

to correctly recognise when there is a 1 in there, it knows when there is a 0 in there, but i think when it runs either im not setting it as the correct type, or one of the previous Dlookups is not getting the correct value.

 

[kella101]

Hi, i think i have eventually sorted it,

Do Until select_part_rst.EOF



num = num + 1
SysCmd acSysCmdUpdateMeter, num


dbscurrent.Execute "DELETE * FROM markup3"


StrSql = ""
StrSql = StrSql & " INSERT INTO markup3 SELECT TOP 1 JDE_Customer_No AS [JDE Customer],"
StrSql = StrSql & " Custom2.JDE_Price_Group As [Cust group],"
StrSql = StrSql & " Custom2.Customer_Name AS [Cust name],"
StrSql = StrSql & " part.price_group As [Part group],"
StrSql = StrSql & " price_factor.use_fixed_price AS [Use fixed2],"
StrSql = StrSql & " IIf(IsNull([Use fixed2]),0,price_factor.use_fixed_price) As [Use fixed]"
StrSql = StrSql & " FROM Custom2, part, price_factor"
StrSql = StrSql & " WHERE part.id = " & "'" & select_part_rst!part & "'"
StrSql = StrSql & " AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'"
StrSql = StrSql & " AND Custom2.JDE_Price_Group = price_factor.customer_group"
StrSql = StrSql & " AND price_factor.part_group = part.price_group"
StrSql = StrSql & ";"

Debug.Print StrSql
dbscurrent.Execute StrSql

StrCustgrp = CustomerSelection

StrStatus = "0"
StrStatus = Nz(DLookup("[Use fixed]", "markup3"))

If StrStatus = "1" Then
StrTableName = "fixed_price_list"
Else
StrTableName = "price_list"


End If

queryString2 = ""
queryString2 = queryString2 & " INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No],"
queryString2 = queryString2 & " Custom2.Customer_Name AS [Customer Name],"
queryString2 = queryString2 & " part.id AS Part,"
queryString2 = queryString2 & " price_factor.use_fixed_price AS [Use_fixed],"
queryString2 = queryString2 & " iif(Use_fixed =1,fixed_price_list.price,price_list.price) AS [Base Price],"
queryString2 = queryString2 & " iif(Use_fixed =1,price_factor.fixed_price_factor,price_factor.factor) AS [Factor],"
queryString2 = queryString2 & " iif(Use_fixed =1,fixed_price_list.min_quantity,price_list.min_quantity) AS [Price Break],"
queryString2 = queryString2 & " part_select.quantity AS [Quantity Required],"
queryString2 = queryString2 & " part.unit_id As [Unit],"
queryString2 = queryString2 & " iif(Use_fixed =1"
queryString2 = queryString2 & " ,(price_factor.fixed_price_factor*(fixed_price_list.price*FixedCurrencyConEUR.sterling_exchange_rate))"
queryString2 = queryString2 & " ,(price_factor.factor *(price_list.price*CurrencyConEUR.sterling_exchange_rate))) AS Price,"
queryString2 = queryString2 & " part_select.BOM,"
queryString2 = queryString2 & " part_select.BOMName,"
queryString2 = queryString2 & " part_select.description As [BOM Description],"
queryString2 = queryString2 & " part_select.[BOM Seq] As [BOM Seq],"
queryString2 = queryString2 & " part_select.RS1,"
queryString2 = queryString2 & " part_select.RS2,"
queryString2 = queryString2 & " part_select.RS3,"
queryString2 = queryString2 & " part_select.info,"
queryString2 = queryString2 & " part_select.Error,"
queryString2 = queryString2 & " part.lead_time As [Lead Time],"
queryString2 = queryString2 & " part.UK_Stock As [Stock]"
queryString2 = queryString2 & " FROM part_select, part, fixed_price_list, price_list, price_factor, Custom2, CurrencyConEUR, FixedCurrencyConEUR"
queryString2 = queryString2 & " WHERE part.id = part_select.part"
queryString2 = queryString2 & " AND " & StrTableName & ".part_id = part.id"
queryString2 = queryString2 & " AND Custom2.JDE_Price_Group=price_factor.customer_group"
queryString2 = queryString2 & " AND price_factor.part_group=part." & PriceGroup
queryString2 = queryString2 & " AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'"
queryString2 = queryString2 & " AND " & StrTableName & ".min_quantity <= part_select.quantity "
queryString2 = queryString2 & " AND " & StrTableName & ".nationality=" & nationality
queryString2 = queryString2 & " AND part.id = " & "'" & select_part_rst!part & "'"
queryString2 = queryString2 & " AND part_select.quantity = " & select_part_rst!quantity
queryString2 = queryString2 & " AND part_select.BOM = " & "'" & select_part_rst!BOM & "'"
queryString2 = queryString2 & " AND part_select.[BOM Seq] = " & select_part_rst![BOM Seq]
queryString2 = queryString2 & " ;"



select_part_rst.MoveNext
Debug.Print queryString2
dbscurrent.Execute queryString2

Loop

this appears to work anyway. However, my access file has now increased to over 300mb, can anyone tell me why?

 

[DCrake]

Simple Software Solutions

Hi Kella,

Do you know what? by the end of this exercise you will be ready to tackle anything....

Apart from not knowig what it's original size was I would suggest that it is to do with bloating and caching. Set the Compact on Close option to true in your options screen (Tools > Options... General)

This will compact the database each time you close it.

David

 

[kella101]

Hi the original size was only 8mb now its 348mb! dont know how that has happened?

 

[DCrake]

Simple Software Solutions

What's the size after you have compacted it?

 

[kella101]

Hi, sadly im using Access 97 which does not have this function. :S

 

[DCrake]

Simple Software Solutions

Morning Kella,

If you create a new macro that compacts a database then convert the acro to visual basic and then copy the code to your exit code of the app it will automatically compact on close. All this time and I didn't know you were still on Access 97.

I will have a search for the code if you are struggling to do it. If you run a compact mannualy what do it resize to?

David