Sql for access query 1

[Marleyuk]

Im importing a file into access and i want to add querys. The problem is the field im querying wont always be in the same place or be the same name. The only thing that remains the same is that the field i want to query will always be the last column. Anyone know what to do for this??

Thanks
Marley.

 

[JerryKlmns]

Possitioning of the the field doesnt matter at all. The name is a problem! Can't you create an import specification to get the file into a permament table?

 

[Marleyuk]

the problem is that with each import the amount of rows and columns will change.. the file been imported shows the data from different nodes so depending on how many nodes are tested the amount of data will change.

 

[JerryKlmns]

You could create a form with a combobox and a command button.
The combobox should be populated with all table names, so you can pickup the one of interest. When pressing the button you could pick up the last field name of that table and create an appropriate SQL statement for a new query created on the fly. Then ... open your very desired querry.

There are many threads on how to get all table names using DAO and the TableDefs method with a For Each Loop. Also about how to create a query with code and open it. Of course you could find examples on the help files. So, give it a try and post back for extra help.

 

[JerryKlmns]

If you are still figuring out....

Set this as the recordsource of the combo

SELECT Name FROM MSysObjects WHERE (((Left([Name],4))<>"MSys") AND ((Type)=6 Or (Type)=1));

and on the click event of the command button add this code

Dim tbl_Field As String
Dim qdf_My As DAO.QueryDef

tbl_Field = CurrentDb.TableDefs(Forms!frmQDFCreate!cboTables).Fields(CurrentDb.TableDefs(Forms!frmQDFCreate!cboTables).Fields.Count - 1).Name
On Error Resume Next
Set qdf_My = CurrentDb.QueryDefs("qdfDesired")
If Not qdf_My Is Nothing Then DoCmd.DeleteObject acQuery, "qdfDesired"
Set qdf_My = CurrentDb.CreateQueryDef("qdfDesired", "Select Sum([" & tbl_Field & "]) From " & Forms!frmQDFCreate!cboTables & ";")
Set qdf_My = Nothing
DoCmd.OpenQuery "qdfDesired", acViewNormal, acReadOnly

Every time the button is pressed, if the query qdfDesired, exists, gets deleted. I hope there won't be more than one pressing the button

 

[Marleyuk]

what does this line do??

SELECT Name FROM MSysObjects WHERE (((Left([Name],4))<>"MSys") AND ((Type)=6 Or (Type)=1));


Also someone told me that the following method should work..


-- This returns the first row which is headers
SELECT TOP 1 * FROM myDumpTable

-- Combined with the data you want
UNION ALL

SELECT * FROM myDumpTable A WHERE

-- If the first column is the 'seconds' column test that column for the value you are looking for
IIF((SELECT top 1 field1 FROM from myDumpTable) = 'seconds', field1 <> 'seconds' AND field1 > 0.001,

-- If the second column is the 'seconds' column test that column for the value you are looking for
IIF((SELECT top 1 field2 FROM from myDumpTable) = 'seconds', field2 <> 'seconds' AND field2 > 0.001,

-- If the third column is the 'seconds' column test that column for the value you are looking for
IIF((SELECT top 1 field3 FROM from myDumpTable) = 'seconds', field3 <> 'seconds' AND field3 > 0.001,

-- If the fourth column is the 'seconds' column test that column for the value you are looking for
IIF((SELECT top 1 field4 FROM from myDumpTable) = 'seconds', field4 <> 'seconds' AND field4 > 0.001,

-- If the fifth column is the 'seconds' column test that column for the value you are looking for
IIF((SELECT top 1 field5 FROM from myDumpTable) = 'seconds', field5 <> 'seconds' AND field5 > 0.001,

-- Else return no row
1 = 0)))))


But im having some kind of syntax error. Can i email you a test zipped version of the database and you can see if you can stop the problem??

 

[JerryKlmns]

There is a system (hidden) table named MSysObjects where all objects of the db exist. I 'm filtering only the objects of type 6 (=linked tables) or 1 (=local tables) which are not named like MSys* (=system tables one of which is MSysObjects). The select statement returns only the names that you need.

About that someone else stuff ......

SELECT TOP 1 * FROM myDumpTable

returns the first record

this

SELECT * FROM myDumpTable Where 1=2;

returns no records, but you still have the fields names. I think the rest is some pseudocode, can't decipher it as SQL.

About emailing ... sorry I can't post my email address.

Also you haven't defined the exact aggregate function you need so in my SQL statement

Set qdf_My = CurrentDb.CreateQueryDef("qdfDesired", [b]"Select Sum([" & tbl_Field & "]) From " & Forms!frmQDFCreate!cboTables & ";"[/b])

I assumed you needed a sum. And I thought that the field name is not to be the same.

 

[Marleyuk]

It returns only that names i need?? How do i know what name i need? I understand about the email address thing, id also avoid it.

Sorry to not be getting it but what does this do then?

Set qdf_My = CurrentDb.CreateQueryDef("qdfDesired", "Select Sum([" & tbl_Field & "]) From " & Forms!frmQDFCreate!cboTables & ";")

Thanks for your help.

Marley.

 

[JerryKlmns]

The combo box, has all the table names in the db, one of which is your just imported. Choose one table name from the combo. The variable tbl_Field holds the last field of the table you have choosen. The line just creates a query with the name qdfDesired with the appropriate sql statement.

Have you tried it yet?