Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 1st, 2007, 08:44 AM
Registered User
 
Join Date: Oct 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dynamic SQL Query Problem

Hi, I'm trying to diagnose a loss of data somewhere in my database.
I have two tables with one ("Import_25") which is built from data in
the other ("Tab F1"). The data from Tab F1 comes from two fields and
is put into a column in Import_25 based on an Index number from Tab
F1. I have built a function which returns the proper comparison to be
used in the SQL statement (essentially it uses a sub-query to look up
the field name based on the index number passed in. - see below). I
am fairly certain that the Function is always returning the correct
comparison and yet I am not getting the results I expect from the
Query.

Here is the code I am using:
(Note: Although the strings have line breaks in this post, they are
note present in the actual code, they are there for readability.)
Code:
Public Function RolloverDiagnosticReport()
...
    'Build SQL query string
    strSQL = "SELECT"
    'Data Field(s)
    strSQL = strSQL + " Entity_List.[Entity Number], [Tab F1 -
Deferred Rollfwd - Balances (LC)].Index, [Tab F1 - Deferred Rollfwd -
Balances (LC)].Year, [Tab F1 - Deferred Rollfwd - Balances
(LC)].Month, [Tab F1 - Deferred Rollfwd - Balances (LC)].[G TU
YTD-PM], [Tab F1 - Deferred Rollfwd - Balances (LC)].[G TU],
[Import_25].[CASH]"
    'Source Table(s)
    strSQL = strSQL + " FROM Entity_List INNER JOIN ([Tab F1 -
Deferred Rollfwd - Balances (LC)] INNER JOIN [Import_25] ON
[Import_25].[Entity Number] = [Tab F1 - Deferred Rollfwd - Balances
(LC)].[Entity Number]) ON Entity_List.[Entity Number] = [Tab F1 -
Deferred Rollfwd - Balances (LC)].[Entity Number]"
    'Conditional Statement(s)
    strSQL = strSQL + " WHERE ([Tab F1 - Deferred Rollfwd - Balances
(LC)].Year = " & intYear & " AND [Tab F1 - Deferred Rollfwd - Balances
(LC)].Month = " & intMonth & " AND
BuildImportTableCondition('Import_25', ' <> [G TU YTD-PM] + [G TU]',
[Tab F1 - Deferred Rollfwd - Balances (LC)].Index))"
    'Order Statement(s)
    strSQL = strSQL + " ORDER BY Entity_List.[Entity Number], [Index];"

    Debug.Print strSQL
    rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
    MsgBox (rs.GetString())
End Function

'Function to return the SQL string which will be used as the conditional statement
'in the SQL query
Public Function BuildImportTableCondition(strImportTableName As
String, strCondition As String, intMapIndex As Integer) As Variant
    Dim rs As New ADODB.Recordset
    Dim cnn As New ADODB.Connection
    Dim strSQL As String
    Dim varReturnCond As Variant
    Dim strRecord As String

    'Build Database connection
    Set cnn = CurrentProject.Connection

    'Build SQL statement to determine the Column Heading for the supplied Index
    strSQL = "SELECT TOP 1"
    'Data Field(s)
    strSQL = strSQL + " [Column Heading]"
    'Source Table(s)
    strSQL = strSQL + " FROM [Import Index Map]"
    'Conditional Statement(s)
    strSQL = strSQL + " WHERE [Index] = " & intMapIndex & ";"

    'Run SQL statement
    rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic

    'Build SQL Condition
    If rs.EOF Then
        varReturnCond = Null
    Else
        strRecord = rs.GetString()
        varReturnCond = "[" + strImportTableName + "].[" +
Left(strRecord, Len(strRecord) - 1) + "]" + strCondition
    End If

    BuildImportTableCondition = varReturnCond
End Function
 
Old October 1st, 2007, 11:42 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What does the data look like before you run this, and what do you want it to look like after you run this? What does it look like after you run this now that is different from how you want it to look like if this were running properly?



mmcdonal
 
Old October 1st, 2007, 11:54 AM
Registered User
 
Join Date: Oct 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by mmcdonal
 What does the data look like before you run this, and what do you want it to look like after you run this? What does it look like after you run this now that is different from how you want it to look like if this were running properly?
I do not want to change any of the data yet. I first want to be able to filter out only the rows which the sum of two fields ([G TU YTD-PM] and [G TU]) from the "Tab F1" table does not equal the corresponding field in the "Import_25" table, based on the Index field from "Tab F1". This SQL statement will be used to feed a Report which displays only the non-matching records.

The data that I am getting back is essentially all of the records which match my month and year conditions. It is as if the last conditional statement doesn't exist.
 
Old October 1st, 2007, 12:20 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

So you have two tables that have at least these fields:

Tab F1
...
[G TU YTD-PM]
[G TU]
...

Import_25
...
[Some other field]
...

Can you create stored queries and use those? For example, you could create one query like this:

SELECT [Tab F1].IndexID, ([G TU YTD-PM]+[G TU]) AS SumField
FROM [Tab F1]

This would be your subquery. Then you could do a second query that compares the two tables based on the sumfield and the index field, like this:

SELECT Import_25.IndexID, Query1.SumField
FROM Import_25 INNER JOIN Query1 ON (Import_25.Cooresponding = Query1.SumField) AND (Import_25.IndexID = Query1.IndexID)

This shows all records where the sum of the two fields is equal to the third field in the other table, AND the index field values match.

These are stored queries, so there is no need to run anything other than to do a look up to this table, an based on the index fields, run any subsequent actions.

Did I understand your problem correctly?


mmcdonal
 
Old October 1st, 2007, 12:24 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, to be clear, the original data looks like this:

Import_25
IndexID Cooresponding
0 100
1 100
2 100
3 50

Tab F1
IndexID G TU YTD-PM G TU
0 50 50
1 50 50
2 75 50
3 25 25

Query1 (from Tab F1)
IndexID SumField
0 100
1 100
2 125
3 50

Query2 (From Query1 and Import_25)
IndexID SumField
0 100
1 100
3 50

Sorry for my mis-spellings.


mmcdonal
 
Old October 1st, 2007, 12:30 PM
Registered User
 
Join Date: Oct 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The solution above you suggest would work, if the Import_25 table had the IndexNo as a field. Unfortunately it doesn't, which is why I wrote the additional Custom Function to determine which Field we should be comparing the Sum from Tab F1 to. So for each record from Tab F1 we need to compare the Sum of [G TU YTD-PM] and [G TU] to a different field in Import_25.

For clarity here is a basic setup of the tables:

[Tab F1]
<Entity Number> <IndexNo> <G TU YTD-PM> <G TU>

[Import_25]
<Entity Number> <Index#1 Column Heading> ... <Index#n Column Heading>

[Import Map Table]
<IndexNo> <Corresponding Column Heading>

So what I want to do is lookup the Index Number from [Tab F1] (which changes per row) in the [Import Map Table] and use the data I get from there to dictate which field we are comparing in table [Import_25].
 
Old October 1st, 2007, 12:38 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If you could post the original table structure, some sample data, and how the data should look after this runs, that would help resolve this.

Otherwise, create the first query, which does the sum for you, along with any other fields you need to see in Tab F1, and then compare those results to Import_25 instead of creating the query with code.

Also, while building your query string, when you want to concatenate strings, use & instead of +.

HTH

mmcdonal
 
Old October 1st, 2007, 01:08 PM
Registered User
 
Join Date: Oct 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by mmcdonal
 If you could post the original table structure, some sample data, and how the data should look after this runs, that would help resolve this.

Otherwise, create the first query, which does the sum for you, along with any other fields you need to see in Tab F1, and then compare those results to Import_25 instead of creating the query with code.
Here are the original Table Structures:
[Import Map Table]
    Index Column Heading
        1 Some Column
        2 New Column
        3 Another Column
        4 Yet Another

[Tab F1]
    EntityNo Month Year Index G TU YTD-PM G TU
    3642 3 2007 2 250 25
    3642 3 2007 1 420 30
    6325 5 2007 4 5600 100
    6895 3 2007 3 0 0

[Import_25]
    EntityNo Some Column New Column Another Column Yet Another
    3642 500 275 0 0
    6325 0 0 0 5200
    6895 0 0 0 0

The records I would want returned to me would be as follows:
    EntityNo Index G TU YTD-PM G TU <Value from Import_25>
    3642 1 420 25 500
    6325 4 5600 100 5200

I hope this can help illustrate the problem I am having.
 
Old October 1st, 2007, 01:18 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That's closer, but I am not sure how you are matching the following:

EntityNo Index G TU YTD-PM G TU <Value from Import_25>
    3642 1 420 25 500
    6325 4 5600 100 5200


420 + or - or some other operator with 25 (or 30) with 500.
5600 + or - or some other operator with 5200.

You can do a search like this. Will it be the case that only one record in Import_25 will have the value "500" in any of its fields?



mmcdonal
 
Old October 1st, 2007, 01:43 PM
Registered User
 
Join Date: Oct 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by mmcdonal
 That's closer, but I am not sure how you are matching the following:

EntityNo Index G TU YTD-PM G TU <Value from Import_25>
    3642 1 420 25 500
    6325 4 5600 100 5200


420 + or - or some other operator with 25 (or 30) with 500.
5600 + or - or some other operator with 5200.

You can do a search like this. Will it be the case that only one record in Import_25 will have the value "500" in any of its fields?
Again, no. There are many records in the table which will have the same values in multiple fields. Also there is no definite way to determine how much the numbers will be off by or if at all (due to the possibility of bad calculations during population of Import_25).

I would match the above records on two conditions: 1) the EntityNo from Tab F1 equals the EntityNo from Import_25; and 2) the sum of [G TU YTD-PM] and [G TU] does not equal the value in the column defined by the Index in Import Map Table.

So as an example:
EntityNo 3642 should have the following values in Import_25
    [Some Column] = 450
    [New Column] = 275
    [Another Column] = 0
    [Yet Another] = 0

But since it doesn't I want it to be picked up by the SQL statement and output to the report. But I do not want all of the fields from [Import_25] to be in the report, only the ones that don't match their [Tab F1] counterparts.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic Table Name in SQL Query drani C# 2 November 6th, 2007 05:47 PM
ASP dynamic SQL query with for loop? hman Classic ASP Databases 3 December 13th, 2006 08:56 PM
SQL Query problem caterpillar SQL Server 2000 2 August 2nd, 2006 07:59 PM
SQL query problem - Need Help Settt Biztalk 0 September 12th, 2005 09:32 AM
Dynamic query problem drex10 SQL Server 2000 2 April 6th, 2004 11:55 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.