Re: Creating a temporary table/query for subform

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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..

"Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:%23o%2304%239MGHA.2064@xxxxxxxxxxxxxxxxxxxxxxx
Don't your users each have their own front end?

--
Duane Hookom
MS Access MVP
--

"Jimbo" <lemaySPaMZap@xxxxxxxxxx> wrote in message
news:vOlJf.13930$2O6.3136@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:%23KrILM9MGHA.2828@xxxxxxxxxxxxxxxxxxxxxxx
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

--
Duane Hookom
MS Access MVP
--

"Jimbo" <lemaySPaMZap@xxxxxxxxxx> wrote in message
news:A9lJf.13928$2O6.10752@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:OjoiNQ8MGHA.1032@xxxxxxxxxxxxxxxxxxxxxxx
If you really need a temporary table that can't normally be seen, try
SELECT tblA.* INTO usystblA
FROM tblA;


--
Duane Hookom
MS Access MVP
--

"LeMay" <lemayNOSPAM@xxxxxxxxxx> wrote in message
news:3oiJf.33743$H71.3699@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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..













.

Relevant Pages

  • Re: IIf problem with <>
    ... I assume that your checkbox is in the header, and basically, what you want ... is to either turn off the filter or turn it on. ... selecting a tick box that indicates show completed jobs ... You are trying to construct a criteria cell for the query ...
    (microsoft.public.access.queries)
  • Re: Creating a temporary table/query for subform
    ... You might be able to normalize your tables using a union query. ... What this table will do is summarize the data they've filtered and then ... I also need to label each checkbox field to something usable ... the database window unless you have chosen to display system objects. ...
    (microsoft.public.access.queries)
  • Re: Use Select query as LinkCriteria with OpenForm method?
    ... You have a form with a checkbox on it ... I thought that by including my query as part of the string ... The filter is ... > qselFilterPests the other fields on the form will not have anything to link ...
    (microsoft.public.access.queries)
  • Creating a query using existing attributes
    ... to create a query that would prompt the user to select the specific ... attributes to filter the data that they want to view. ... to have to enter the attributes each time but simply click on the checkbox ...
    (microsoft.public.access.queries)
  • report generating duplicates
    ... I've written a query that will ... filter out all records except duplicates. ... continuously, preceded by a checkbox. ...
    (microsoft.public.access.formscoding)