Query Not Responding

[Lady_Jane]

Hi, I have a couple of queries that look something like this:


Query1

SELECT a.SampleID, b.Taxa, a.Subtable
FROM tblAll as a, tblAll as b;

Query2

SELECT Query1.SampleID, Query1.Taxa, tblAll.Count, Query1.Subtable
FROM tblAll RIGHT JOIN (Query1.SampleID = tblAll.SampleID) AND (tblAll.Taxa = Query1.Taxa);

tblAll has around 39,000 records, and Query1 should result in about 500,000 records. Query1 has successfully run (i.e. shows up in datasheet view) but still takes ages (I haven't left it to complete) to produce or display all the rows. I have now converted Query2 to a make table query so that I won't have to run it over and over.

It's been running for about 4 hours now and Access says it is not responding, but I think it's still going.

I'm new to access and am trying to understand what it is that makes the query slow down so much. Is it simply because I am trying to create such a large number of records?
Can you see any way to edit the above query(ies) to make anything run faster?

It turns out that Queries 1 and 2 should result in roughly 1.9 million rows each. Is this way too many?

[weekend00]

if tableAll has 39,000 records, Query1 will return 1,521,000,000 records.

Query2 will return something SYNTAX ERROR. Please check the syntax of join and fix it.

after fix, query2 will return 59,319,000,000,000 records.

I think you are very new to Access, so may be you should tell us what you want to get from the table and then we can help.

[Lady_Jane]

I am certainly an Access "Novice"
The queries posted actually came from another thread that you (I believe) helped me out with:
https://www.accessforums.net/queries...tion-8264.html

I have tblAll which has the following fields:
SampleID, Taxa, Count, SubTable
Composite primary key of SampleID and Taxa

The end result ideally will be one table with SampleIDs as column header and taxa as row header, with the count of each taxon making up the table.
There are, however, about 3000 samples. So the data are divided into subtables. I need to make sure that each subtable, has the same list of taxa, regardless of the count.

(I suppose I could also have taxa as column headers and samples as rows, but I think there was a reason for my not having done that before.)

I tried to use the same queries as the thread I posted above, but it is returning too many records.

I hope this makes sense, thanks for your help.

[weekend00]

you have 3000 samples, and did you mention there are at most 256 columns in a query? then you need 12 crosstab queris, is this what you want?

Do you have any problem to follow the solution in the other thread?

[Lady_Jane]

Yes, 12 crosstab queries is what I would like.

The problem I am having is that in the previous thread, I first divided the data into 3 different tables, and then created 3 or 4 crosstab queries for each. This means that there were fewer taxa and fewer samples for each of the 3 tables.

Now my task it to take all of the original data without dividing it into 3 tables, and perform the crosstab queries. When I run the first query from the previous thread in order to create a table in which every sample has every taxon I get a memory error.

[weekend00]

I forgot how I designed those queries. let me think about them.

Would you please attach a sample database here?

[weekend00]

The first query is very unefficient, maybe that cause the memory error. let me think about a better one.

[weekend00]

Please try this set of queries:

Query: Makeup

select sampleID,Taxa,Ccount,SubTable from TblAll 
UNION ALL 
SELECT a.minOfsampleID, b.Taxa, Null , a.SubTable FROM 
    (select min(sampleID) as minOfsampleID,SubTable from tblAll group by subTable) AS a, 
    (select distinct Taxa from tblAll ) AS b

CrossTab Query:

TRANSFORM Sum(MakeUp.Ccount) AS SumOfCcount
SELECT MakeUp.Taxa
FROM MakeUp
WHERE (((MakeUp.SubTable)=1))
GROUP BY MakeUp.Taxa
PIVOT MakeUp.sampleID;

[Lady_Jane]

Thanks!
That looks to be working perfectly.

Instead of creating a table where each sample contains every taxon, you are creating a table where each subtable contains every taxon, right?

[weekend00]

you are right.
Before, I create every taxa for every sampleID, that makes a huge number of records.

in the later one, I only create all taxa for one sample id in each subTable.