Generate a query from a table that has no data

[juniorz]

Hello,

I currently have tables that are in the database but they require the user to import the data from an excel file. This works fine however due to the data being imported I only require certain columns from the import, this is where I would use a query.

The data of the import will always have the same column headings. Firstname, lastname, usernumber etc..

Unfortunately i'm not quite sure how I could get a script to generate a query of the selected columns after an import is completed.

any help appreciated

 

[plog]

Your jargon is getting in way of your idea.

The data of the import will always have the same column headings...i'm not quite sure how I could get a script to generate a query of the selected columns...

If you know the field names, it will be easy to write a query. Why do you want a script to do this? And for what purpose?

Can you explain what you want in simpler terms? If you could provide sample data that would be helpful too.

 

[juniorz]

haha sorry, i'll try again.

The data is of student test results, it is used to track the student over several years and compare progress. these are exported from the internet into an excel document. From this document I then Import it into a blank table on the database. The table is already named and created it just requires the import.
From this import, it creates my cloumn headings. Normally once it is imported I could go and create my queries choosing the headings of the table. However as the table has not had the data imported to it, how can I tell the query to get the column heading.

Essentially what I am after is once I import data into a table, A query is created without having to do the wizard, and as more tests are done throughout the year the query continues to collate data from the tables.

Hope this makes sense.

 

[plog]

This part seems contradictory:

The table is already named and created it just requires the import.
From this import, it creates my cloumn headings

If the table exists, the field names (column headings) inside the table exist and should be known to you. You make it sound like the field names change with each import. How can that be?

If you had a table like this:

StudentGrades
StudentName, StudentClass, StudentGrade, GradeSemester
Sally, Algebra, C, 18
Dave, History, B, 18
Tim, Geometry, A, 17

And you deleted all the data, then reimported data into it, all the queries you had built using StudentGrades would still work because none of the field names (StudentName, StudentClass, etc.) would change with the import, just the values going into them (Sally, Dave, Algebra, History, etc.).

 

[juniorz]

I have created the tables, but their isn't any field names in the tables. That is why I wait for the import to populate them.

adding the field names before hand will be difficult as each test is a different size of questions and as results of the questions are on the import it could fail the import.

 

[plog]

I have created the tables, but their isn't any field names in the tables

That is not possible. Can you post what you have?

 

[juniorz]

tablename:year10test10
fields: ID (default field)

The command I run to import into the table:

DoCmd.RunSQL "DELETE * from year10test10"
DoCmd.Transferspreadsheet acimport, acSpreadsheetTypeExcel19, "year10test10", selectfile, true, "Worksheet!A11:BZ70"

 

[plog]

Can you give an example of the query you want to run? I mean if you don't know what fields are in the table, then how do you know what do you want in your query?

You can always run this query:

SELECT * FROM year10test10

And it will pull in all the fields no matter what they are.

 

[The_Doc_Man]

This smells highly of a denormalized table. Please read up on database normalization. The reason you don't have field names is because your table is not normalized. In a normalized table, what you describe cannot happen. That is why you are getting comments about how your problem is confusing to some of us.

In a table that isn't even 1st Normal form, this situation could easily happen. It is one of the (many) reasons that normalization is valuable to know and understand.