Creating a temporary table/query for subform

[LeMay]

I'm trying to create a subform that the user can sort by clicking on the
column headers.

Before I get to the user side, the code part has become a problem. My table
headers look like the following. ID is the primary key:

ID, Name, Value

I need to make a temporary column that shows percentage; I'll call it
percent. Unfortunately, because it is in a mutliuser environment, I cannot
insert the percentages into the table itself, because each user would have
different percentages. Therefore, I need to create a temporary copy of the
table for each user and add the fourth column called "Percent."

As another requirement, I cannot let this temporary table or query show up
in the list of objects showing up when you open up the database.

So what strategy should I use?
I thought about using a make-table query using a QueryDef, but I can't find
any help in the MS Access help on how to do this in VBA.

Anyone know how to make this query so it can be used in the subform?

Please reply in the newsgroup.

... Jim..

 

[Duane Hookom]

If you really need a temporary table that can't normally be seen, try
SELECT tblA.* INTO usystblA
FROM tblA;

 

[Jimbo]

Duane,

Thanks for the quick reply... here's what I tried:

Dim qdf As DAO.QueryDef
Dim strSetService As String
strSetService = "SELECT * INTO TestQry FROM tblServices;"
Set qdf = CurrentDb.CreateQueryDef("", strSetService)
qdf.Execute
qdf.Close

It did create the table, but it showed up in the database object list
anyway, something I am trying to avoid. How can I prevent it from showing up
there? And how do I detect if a table already exists?

... Jim..

 

[Duane Hookom]

Why do you need to detect if the table already exists? Don't you just want
to write over it?

I should have explained a bit more about why I chose "usystblA" as the table
name. Any object name beginning with "usys" is not displayed in the database
window unless you have chosen to display system objects.

You should be able to use:

DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT * INTO usysTestQry FROM tblServices"
DoCmd.SetWarnings True

 

[Roger Carlson]

Maybe I'm missing something, but why can't you base your subform on a query
where the percentage is calculated? I don't see why you need the temp
table.

BTW, when I need a temp table in a multiuser environment, I will
programmatically create a local database (root of the C drive), export an
empty table to it, then relink the temp table back into the program. Then
the user has a local table.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImportToTempDatabase.mdb" which uses a temp database and temp
table to import a text file without bloating the database.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jimbo]

Thanks again for the reply.

The table existence test was another question in case other things didn't
work out. In a multiuser environment, I'm wanting to avoid users accessing
the this new table because each user is going to have different data that
they customize in one column. I really don't want them in the end sharing
the same table. Perhaps a temporary table that exists for a split second
would be okay, but it would have to lock out another accessing user trying
to run the same procedure at the same time.. My goal was really to copy the
whole table into memory as another table (with a column to be added) to keep
other users from accessing or having conflicts with it. Then I could link it
into a subform.

Thanks for the "usys" prefix. I didn't know about that.

... Jim ..

 

[Duane Hookom]

Don't your users each have their own front end?

 

[Jimbo]

No, they use the same form. What happens is they filter down data at will.
What this table will do is summarize the data they've filtered and then
display the sums in a subform. The "Name" and "ID" fields are static data
(to be row headers), they never change. The value fields ("Count" and
"Percent") change depending on what the users want to filter.

There's probably a better way to do this. Unfortunately, the designer of the
data entry is easy for data entry personnel, but not so easy for me as I
summarize data. The data is collected about people. The surveys given to
these people used checkboxes to collect data -- almost 50 checkboxes per
person in the main table. My job is to summarize these checkboxes, but
database users are allowed to filter them down by region. They want to see
the whole list sorted by the ones with the highest count in a column format
(rather than a row format.) so that each different checkbox field is on a
row. I also need to label each checkbox field to something usable for the
user who is going to read it, and that's where the problem starts. I have a
separate checkbox summary table that has checkbox numbers and checkbox
names, but they don't join to the main data table, so I have to manually
associate them in code. My solution was to fill out the summary in another
table (copying the checkbox table first), and using a DCount on the main
table, which field names are:

CheckboxID, Name, Count, Percent

The only way I know to have a sortable list is to use the subform control,
and I believe that requires a table or a query. Since I have to insert data
into this table, doesn't that rule out a query since they are read only? So
I am left with creating a table as the control source. I can delete the
table after the user is finished viewing it.

Maybe there's a better way.

... Jim..

 

[Jimbo]

Thanks for your reply.

I gave Duane the problem that could possibly be done without a temporary
table, I'm not sure yet. Perhaps you can figure out a way.

But I have a another one on this same data that is more perplexing. I have
50 checkboxes in a survey of people. At the end of the survey, people are
asked to rank the top 5 checkboxes they checked as their highest priorty. My
job is to count the number of times each type of checkbox was ranked as a
priorty in any of the five fields for all the surveys. Fortunately, these
five fields identified the checkboxes with ID numbers. So if checkbox #28
appears in the Priorty 2 field, that counts as one entry. If checkbox #28
appears in the next survey in the Priorty 5 field, that also counts as one
entry. As a note, on each survey, the same priorty cannot appear twice in
any of the five fields.

The results of this count should be a vertical subform with the following
headers:

Column: CheckboxID, CheckboxDescription, TotalCount, Percent

The user should be allowed to sort by description or count.

I hope you can figure out a better way to do this.

... Jim..

 

[Duane Hookom]

Your application should be split between front and back end databases and
each user should have their own front end.

If you are referring to a survey application, have you looked at "At Your
Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane?

You might be able to normalize your tables using a union query. The union
query can then be used for summarizing data. If your users want to do lots
data analysis, link the table/query into Excel as the source of a pivot
table.