combining queries into tables for crosstab

Hi, unfortunately the more I read your question the more confused I get. You mentioned that you have 4 queries that you want to combine into one table. Have all 4 queries been created in the one database using the same table? If so, it would be easier to create your crosstab query directly from the original table instead. However, when I read this bit :

I have four queries with columns of coded information: Table for Person 1, Table for person 2,..3, and 4. Each query has about ten columns, one of which is their name for every record.

Click to expand...

... I'm not sure which items are tables and which items are queries.

If you could post how the 4 queries are created (what is different about them) plus details of the underlying data table (the variable names etc) plus if you coulc elaborate on the final crosstab query (i.e. the variable names you want across the top of the query) then someone here should be able to help.

Andrew.

It's sort of different from most other postings here, but let me give it a shot...
There are 4 primary tables for four chimp subjects in a study of signed (American Sign Language) conversation. Each table has a column of videotape time, where each row represents a tenth of a second of the video transcript.
Almost all of my queries from these main tables are designed to answer a similar question: did Person 1 interrupt person 2, if so, When?... One col therefore has a code for who intterupted who (null, CI or HI) where CI means Chimp interrupted human, HI means Human interrupted chimp. For almost everything I need to do, this col with the CIs will be the data I 'count' in a cross-tab. Assume I only want to look at the C's interruptions for now. (I've queried to get rid of all HIs). Now I have a separate query for each of the 4 chimps with only the info on what the human was doing when he/she interrupted him, i.e., CI rows only. Some of the other columns of coded information is stuff like: duration code of the interrupted sign (codes =null,LONG, SHORT, AVERAGE), what percent of the H's sign was complete at the point of interruptin (codes=null, 10, 20, 30...100) and others.

I want to pull 3 columns from each of the four chimp's queries: Chimp's name col which is all the same name, the CI col which is all "CI", and one other col (say duration) so that I endup with one long table where my Col headings are CHIMP, CI, DURATION and I get every occurance of each chimp interrupting during the long, short, avg signs.

I get the message that if I want to pull from several queries I need to first make a table with the combined info. I've done this before but now can't remember how, so I'm cutting and paste appending the records of each chimp into one so that I can get to the cross-tab calculations. However I know I can do some type of query or table design to ask it to take those variables from four different queries and end up with one table of 3 cols, not rather than 1 table of 12 cols. I hope this makes some sense. Sorry for the rambling, it's very hard to describe without visuals.

Hi, yes this does make sense. IMO a fascinating project!

In the absence of VB code (not my forte), a workaround would be to do this with 4 different queries.

You would first need to create a new table with the fields you want in your final analaysis (i.e. Chimp Number, Chimp Interrupt {this field may be surplus given all records will have the same value and it is a table of Chimp interruptions anyway}, Duration, Activity during interruption etc.). Save the table and at this point in time it has no data.

If you create 4 new queries - one for each chimp. The query should be an "append" query - while in design mode Click Query -> Append Query -> Select the table you created -> Ok. Pick up the data you want (i.e. what you specified in your last post) from the chimp1 interrupt query and assign it to the relevant variables in the "append to" row of the query. (Alternatively, you could modify your existing select query to become an append query)

Save and run. This will place the data from your query for chimp 1 into the new table you created.

Repeat for the other 3 chimps. You can then use the data in the new table for your crosstab query or any other "cross chimp" analysis.

Please note that where new data is added to the 4 chimp tables, you might want to delete all of the data in the table before running the queries again (otherwise you will get duplicate entries) - an alternative method is to have a new field (i.e. a yes/no field called "transferred" or something similar in your tables) that gets updated from no to yes when after the 4 queries are run (via separate "update" queries, plus the 4 "append" queries would need to be modified to pick up the "no" items only). All said and done, it may be easier just to delete all of the entries from the table before you re-run the 4 queries.

Apologies for the clunky solution, it's not elegant I know, but it should work. To make it a little less cumbersome, you could include all 4 queries in a macro and run the macro once rather than running the 4 queries separately.

HTH, Andrew.

Thanks Andrew for the recommendations. I'll try it and let you know If I'm able to pull it off - or come back with more questions! (Hope it's ok if I just put a thanks out here!)

Exactly what I needed. Thanks so much! Quentin

couldn't a UNION query have been used?

did you manage to get the columns headings in the order you wanted them?