Counting from queries

[alktrigger]

I'm trying to create a query to create that counts the fields in other queries. I can count any field from any individual query with no problems, but as soon as I add another query to count, it gives produces a single abnormal total across all fields.

When I run this code, it produces a single column with the value "9", which is the actual number of entries

SELECT Count(sqyWeek1Count.SumOfReceived) AS CountOfSumOfReceived
FROM sqyWeek1Count;

But as soon as I add the second query, it comes up with the value of "72" in both columns. It doesn't matter which fields I am counting, it just puts all the values at "72", keep in mind that the second query only has 8 entries. The following is the code when I am bringing the second query into play:

SELECT Count(sqyWeek1Count.SumOfReceived) AS CountOfSumOfReceived, 
     Count(sqyWeek2Count.SumOfReceived) AS CountOfSumOfReceived1
FROM sqyWeek1Count, sqyWeek2Count;

What could be going wrong?

 

[alktrigger]

The change in values are occuring as soon as I add a second query to the design sheet, I don't have to even use any of its fields.

Any ideas? Anyone?

 

[MSAccessRookie]

I'm trying to create a query to create that counts the fields in other queries. I can count any field from any individual query with no problems, but as soon as I add another query to count, it gives produces a single abnormal total across all fields.

When I run this code, it produces a single column with the value "9", which is the actual number of entries

SELECT Count(sqyWeek1Count.SumOfReceived) AS CountOfSumOfReceived
FROM sqyWeek1Count;

But as soon as I add the second query, it comes up with the value of "72" in both columns. It doesn't matter which fields I am counting, it just puts all the values at "72", keep in mind that the second query only has 8 entries. The following is the code when I am bringing the second query into play:

SELECT Count(sqyWeek1Count.SumOfReceived) AS CountOfSumOfReceived, 
     Count(sqyWeek2Count.SumOfReceived) AS CountOfSumOfReceived1
FROM sqyWeek1Count, sqyWeek2Count;

What could be going wrong?

Your First Table has 9 Entries.
Your Second Table has 8 Entries.
The Combined Query has 72 Entries.
​

It looks like a Cartesian Join to me. What Columns are the two Tables joined on? If they are not joined on any particular Columns, then the Query will match each Column in Table1 with each Column in Table2 and get 8x9 or 72 responses.

 

[alktrigger]

i've made a little progress since posting it, but nothing substancial.

I have 9 queries that sumarize 1 week each for number of entries

sqyWeek(1-9)Count:
ShipName
CountOfID (not being used)
SumOfReceived

I'm trying to create another query to read the count of entries of each week in order to create certain data.

I currently have the shipment names linked together, with LEFT JOINs. Then I am just doing a count calculation off of each query. Although this is so far the most successful so far, I noticed that the count from qryWeek(n+1)Count can't be larger than qryWeekCount.

My end goal of this qry is to have an output similar to:

Week1 | Week2 | Week3 | - - - | Week8 | Week9 |
---9-- | ---8 --| --9--- | - - - | --5--- | --7--- |

where each number represents the number of recorded entries for that week. The numbers are currently small due to the fact that the database has not been populated yet.

 

[alktrigger]

I give up, Unless there is a solution, I'm oging to do this the sloppy way and just make 9 amend table queries then query the table, since nothing I have tried is looking like it is working