Selectcoumns

[Lou_Reed]

I am needing to make tables out of a very large MS Access table. I believe that it can be done with the make table query.



I am interested in using the SELECTCOLUMNS statement in a query. The make table query is just
a SELECT query with a make table command at the end.

I have seen online very little info on the SELECTCOLUMNS query. It seems somewhat hard to find out
about.

Anyway, what is the SELECTCOLUMNS statement in a MS Access query and how do I use it?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ranman256]

make a Make table query,
drag in the column you want
save query
run query

dont use sql.

[Lou_Reed]

I want to make this as user friendly as possible. I will not be the end user of this query, someone else will be and they are just going to be just pushing buttons. I will try what you said, but in the end this must be very user friendly to someone else a novice.

When you say drag in the column that I want that means of course user the create query graphical interface.

A make table query is merely a select query with the make table added in at the end is it not?

A help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ranman256]

then you do NOT want Make table. I thought you were converting a 1 time thing.
for continuous use by users, you want to make the table once, (designing)

then the users will use an APPEND QUERY to add their data to the main table.
or SELECT query to view it.
but why would the column choices keep changing?
why wouldnt they want to see all their data?

[Lou_Reed]

Okay, wait. I want to break a very large table in MS Access into a collection of smaller MS Access tables. The break-up of the large table will be done by columns. It does not make sense to me either, but the end user wants it done that way and they will get it that way.

I am not an expert on this, but I think the append query is not appropriate. It assumes (I think) that the smaller tables already exist, they do not. They are created when the make table query executes.

Each column from the large table will make a single smaller table; if there are n columns then there will be n tables.

I just do not see how an append query will work. if you think it does, please explain it to me.

I just want to take a large table and make a bunch of little table from it. The method is one column to each table.

I am not sure how to do this, but I think that your first post had the right idea.

Please elaborate on it.

Thanks in advance.

Respectfully,

Lou Reed

[June7]

One column to each table? Making even less sense.

Why make a bunch of little tables?

Use queries to retrieve whatever field(s) are needed for whatever purpose.

What is the user's ultimate goal?

SELECTCOLUMNS is not valid in Access.

A process that routinely modifies db structure is poor design. It causes db bloat and increases chance of corruption.

[Lou_Reed]

June7:

I could not agree more with you!

It is what the person paying for it wants. I already explained to them that they will get a lot of small tables with no relationships
in them. I am going to give them what they want and hopefully they will see the light and change their mind.

I just need to know how to make a query that does this. I do not need to any more than that. It should be as push button automated as possible.

The person using it does not know MS Access.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

Well, yes a MAKE TABLE action can create table. But I do not understand how you want to employ this. Do you want to allow users to select a field name from a combobox list then run a MAKE TABLE query that pulls that selected field? This cannot be done with a query object. Use VBA to dynamically build SQL statement.

Users who do not understand Access should not be dictating design. They should not care about HOW their needs are met.

[Lou_Reed]

You are correct on all counts. But this is what the sponsor requested and I will give it to them.

After the Excel spreadsheet is imported, all these smaller tables made from the columns of the large table can be created as tables as needed.

I have alerted the sponsor to this still they insist they want it done this way. OK. They want it. They will get it.

Maybe then, they will see what you and I are talking about when I deliver it to them.

Meanwhile one small question, the newly created tables from the columns of the large table look like ... well columns. I would like someway to format them as say a square or pretty a much square table.

I want to get away from columns which is what I have now.

Is there any way to do it?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

.

[June7]

What do you mean by a square?

Tables in Access is how data is stored. Tables have fields and records. You cannot 'get away' from them.

You will probably have to post a picture to express your idea.

[Lou_Reed]

You are right, it is just that I think it would look nicer if the tables do not look like one very long column. That is all.

I know what this sounds like. I am trying to let the sponsor see the error of their ways.

I just thought that I could improve the looks of the tables. That is all.

But you are right they tables are formatted and you cannot get away from that.

Again, let me contact the sponsor and see what they want.

Meanwhile, what is a nice way t maker a long column of data look more appropriate?

I am just ranking my mind over how to make this look better.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

More appropriate by what standard? What is wrong with a long column of data? It is what it is - a column of data.

User asks for something bizarre - he gets bizarre.

[John_G]

Again, let me contact the sponsor and see what they want.

That is the key to all this. It sounds to me as if the sponsor has no clue about what databases are and how they work. If you can deliver WHAT the sponsor wants, then the HOW part of it should not matter to them. If they want a push-button application, that's fair enough, and can usually be done, but how it's done shouldn't matter. Just my opinion.

As June7 has asked - what could they possibly want to do with a whole bunch of small tables of one column each? It makes no sense to me either.

I would like someway to format them as say a square or pretty a much square table.

About the only way I can think of to do that is a multi-column report, similar to what you might do to make labels but with only one field.

If you end up really having to do it, the process to make n tables from a larger table with n fields could be automated with a relatively small VBA procedure. I'll see what I can come up with.

[June7]

Of course, as John_G indicated, use multi-column report to 'improve' the appearance of data for print output. Doesn't change how data is stored, still a single column in table. This same report could be sourced by a larger multi-field table. Again, the 'how' should not matter - if user wants output of only one field, that can be accomplished from a normal data structure.

[John_G]

Here is a sample bit of code to split the larger table into single-field tables. I did a quick test and it works.

Have your sponsors considered what they will get if the original table column contains a lot of blanks?

Sub Split_table()
  Dim fld As Field, SQL As String, rst As Recordset, ColumnName As String
  Set rst = CurrentDb.OpenRecordset("Main_Data_Summary")
  For Each fld In rst.Fields
    ColumnName = fld.Name
    On Error Resume Next  'Ignores error that would occur if the table does not exist
    '
    ' Delete current version of the table (if there is one)
    '
    DoCmd.DeleteObject acTable, ColumnName
    On Error GoTo ErrProc   ' Reset error trapping
    '
    ' Create SQL to create the new table (a make-table query)
    '
    SQL = "Select [" & ColumnName & "] Into [" & ColumnName & "] from Main_Data_Summary "
    Debug.Print SQL
    CurrentDb.Execute SQL, dbFailOnError
  Next
  rst.Close
  Exit Sub
ErrProc:
  MsgBox Err.Description
  rst.Close
  Exit Sub
End Sub