Interactive Query (1 Viewer)

[Richhol65]

Hi There

Hopefully someone will be able to help me.

I am importing a set of tables to a database every month, I then need to extract data from these tables but each month the make up of the columns could be slightly different.

The constants are as follows:

The first 5 columns are always the same and the final column also, I need a way to extract data from the columns in between these two constants. Sometimes there may be one column in between others there may be 20.

Is this possible ?

Thanks in Advance

Rich

 

[namliam]

You can change your design view to SQL view and instead of listing individual columns you change it to YourTableName.*, which will select all columns.

Otherwize you can add some VBA to more or less do the same but the first idea is much simpler.

 

[Richhol65]

Hi There

Thanks for the reply - I dont want all columns I just want the data from the columns between the constant columns

I will use one of the first 5 columns as it has an identifier in it then i want to get the data from the columns that are between the two sets of constant columns - If I wanted all the data I could just use the original tables.

This data will be added to a table with three columns which would be the Identifier, the name of the data column and the data that relates to the identifier in that data column e.g.

Identifier, Column Name, Data
123456, DED6, 0.25

Cheers

Rich

 

[namliam]

in that case can you not just do
Select Identifier, Columnname, data
from yourtable?

Sorry dont really understand the issue I think, Perhaps post a sample DB?

 

[Richhol65]

Hi There

please see below example excel spreadsheets that would mirror the tables in access:

Month 1: Has 3 columns after the standard
Month 2: Has 4 columns after the standard so each month the tables can have additional or less columns and I want a query that will get the data from the data columns and detect when it gets to say the total column and then stops copying data

Does that make any sense

 

[Cotty42]

Hi Ricchol65

If you are running your import by clicking on a button on a form then the following code should work fine.

You will need to set up a linked Excel spreadsheet (I've called this DEDMonthlyImport.xls with the corresponding link in Access call lnkDEDMonthlyImport) and overwrite the spreadsheet each month with your monthly import spreadsheet but keep the same name (DEDMonthlyImport.xls). I am importing the data into a table called tblDEDList which has 3 fields, Identifier, Column Name, Data Value.

Private Sub btnMonthlyImport_Click()
On Error GoTo Err_btnMonthlyImport_Click

    For i = 1 To 20
        strFieldName = "DED" & LTrim(Str$(i))

        ' *** Check to see if field exists in import table.
        DEDExists = DLookup(strFieldName, "lnkDEDMonthlyImport")
        
        ' *** build SQL to copy values into DEDList table
        strInsertText = "INSERT INTO tblDEDList ( Identifier, [Data Value] ) "
        strSelectText = "SELECT lnkDEDMonthlyImport.Emp, lnkDEDMonthlyImport." & strFieldName & " "
        strFromText = "FROM lnkDEDMonthlyImport "
        strWhereText = "WHERE (((lnkDEDMonthlyImport." & strFieldName & ")>0));"
        strSQLText = strInsertText & strSelectText & strFromText & strWhereText
        
        DoCmd.SetWarnings Off
        DoCmd.RunSQL strSQLText

        ' **** Add column name to DEDList Table        
        strUpdateText = "UPDATE tblDEDList SET tblDEDList.[Column Name] = '" & strFieldName & "' "
        strWhereText = "WHERE (((tblDEDList.[Column Name]) Is Null));"
        
        strSQLText = strUpdateText & strWhereText
        DoCmd.RunSQL strSQLText
        DoCmd.SetWarnings WarningsOn
        
    Next i
    
    Me.Form.Refresh
 
Exit_btnMonthlyImport_Click:
    Exit Sub
 
Err_btnMonthlyImport_Click:
    ' **** Trap error if field does not exist on DLookup and exit sub
    If Err.Number = 2471 Then Resume Exit_btnMonthlyImport_Click

    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_btnMonthlyImport_Click
    
End Sub

Hope this helps.

Cheers
Dave

 

[Richhol65]

Hi Dave

Thanks for this will go an try it now.

Each table will always have the same first 5 columns which apart from the identifier I wont be using and the same last column which I won't be using.

Is there any way to get this to start at column 6 and end at last column -1 ?

Rich

 

[Cotty42]

It doesn't start at Column 1 the loop is looking up the DED columns, i.e. i=1 then fieldname is 'DED1', i=2 then 'DED2' etc.

Obviously this assumes that each of the columns in the spreadsheet that you want to read are labelled 'DED1', 'DED2', 'DED3' etc.
so it will only read the 'DED' columns and the employee number as the record identifier.

Dave

 

[Richhol65]

Sorry Dave

I see that now - should look before I leap

Thanks for this

Rich

 

[Richhol65]

Hi Dave

I realise what I have done now. That example I gave was just that an example the column names aren't DED1, DED2 etc they have individual names say Chapel, Medical, PHI etc I only attached the tables to show that it could be a different number of columns each month

Is there a way of doing this ?

Cheers

Rich

 

[Cotty42]

I'm sure there will be but it will take a bit more looking into - I'll have a look at it tomorrow if I get the chance.

 

[namliam]

Why would you need the last column before total? what is the logic behind this?

To get the number of fields in a table using this:
CurrentDb.TableDefs("YourTable").Fields.Count

You can then get the column names using something like
CurrentDb.TableDefs("YourTable").Fields(x).name


Count will return the proper number, i.e. 10
the X used in the second is "zero based" which means that 0 will return the first column name. X in this example will have a max of 9.

So to get the name for the last column in vba:

CurrentDb.TableDefs("YourTable").Fields(CurrentDb.TableDefs("YourTable").Fields.Count
 - 1 ).name

 

[Richhol65]

Hi Dave

Thanks for all your help

Previously i have been able to do something similar but it was populating a table with tablenames rather than field names so is slightly different. I then used that table to create different queries dependant on the tables that existed I was trying something similar here

Cheers

Rich

 

[Richhol65]

Hi Namliam

I need the last column minus 1 as the last column is always total and I wont be using that column so if there are 20 columns in total I would need everything from column 6 to column 19, if there were 15 columns I would need everything from column 6 to column 14 - do you see what I mean ?

Rich

 

[namliam]

For I = 6 to CurrentDb.TableDefs("YourTable").Fields.Count
    debug.print CurrentDb.TableDefs("YourTable").Fields(I -1)
next I

I hope you can add to that so it will suite your needs

 

[Cotty42]

Thanks Namliam, I knew the Gurus would come through.

Based on the previous code try this.

Private Sub btnMonthlyImport_Click()
On Error GoTo Err_btnMonthlyImport_Click
    FieldCount = CurrentDb.TableDefs("lnkDEDMonthlyImport").Fields.Count
    ImportFields = FieldCount - 6
    
    For i = 0 To ImportFields - 1
        strFieldName = CurrentDb.TableDefs("lnkDEDMonthlyImport").Fields(i + 5).Name
        
        strInsertText = "INSERT INTO tblDEDList ( Identifier, [Data Value] ) "
        strSelectText = "SELECT lnkDEDMonthlyImport.Emp, lnkDEDMonthlyImport." & strFieldName & " "
        strFromText = "FROM lnkDEDMonthlyImport "
        strWhereText = "WHERE (((lnkDEDMonthlyImport." & strFieldName & ")>0));"
        strSQLText = strInsertText & strSelectText & strFromText & strWhereText
        
        DoCmd.SetWarnings Off
        DoCmd.RunSQL strSQLText
        
        strUpdateText = "UPDATE tblDEDList SET tblDEDList.[Column Name] = '" & strFieldName & "' "
        strWhereText = "WHERE (((tblDEDList.[Column Name]) Is Null));"
        
        strSQLText = strUpdateText & strWhereText
        DoCmd.RunSQL strSQLText
        DoCmd.SetWarnings WarningsOn
        
    Next i
    
    DoCmd.Requery
Exit_btnMonthlyImport_Click:
    Exit Sub
Err_btnMonthlyImport_Click:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_btnMonthlyImport_Click
    
End Sub

This should work no matter what your columns are called, as long as your import spreadsheet has the same name.

Cheers
Dave

 

[namliam]

Why do an insert followed by an Update? you can do both in one insert query without any problems?

Why do -6 for your importfields, only to do +5 later on? Doesnt make much sence to me!

 

[Cotty42]

Hi Namliam

I have to admit much of what I do doesn't make sense to me either!

But in this case the -6 is used to determine how many fields there are to be imported and the +5 then sets the offset so that it starts importing from field 5.

As for the query - I am in complete agreement and can think of no excuse why I wouldn't have combined both in the one query. I guess its just another alzheimer moment (worryingly they appear to be getting more frequent).

Thanks for the comments.

Dave

 

[namliam]

I understand the -6 and +5 combination, however you can (or could have) solve it like so:

    ImportFields = FieldCount - 1
    
    For i = 4 To ImportFields

Remember it is 0 based thus the 4 is actually the fifth column.