SQL Problems andthe Cartesian Product

I have three tables.  
Table one has one field with one record.  

Table two has two fields, one field relates to table one.   There is one record in this table.

Table three has two fields, one field relates to table one.  There are two records in this table.

I need ONE SQL statement that returns one record.  It would group by table one's record and return the count of records from table one (it should return 1) and the count of records from table two (it should return 2).

The output would look like this:
Tbl1 Fld 1...Tbl2 Fld 2(count)... Tbl 2 Fld 2 (count)
Test..1.................1.......................2

This is what I came up with
Tbl1 Fld1....Tbl2 Fld2(count)...Tbl2 Fld2 (count)
Test...1.............2.......................2
Test...1.............2.......................2
kkirt1Asked:
Who is Participating?
 
lmerrellConnect With a Mentor Commented:
The syntax is:

DCount(expr, domain[, criteria])
DCount("Field2","TableB","Field1 = " & [TableA].[Field1])

expr --> Name of field as string = "Field2"
domain --> Like the source (ie. table name, query name, etc) = "TableB"
criteria --> specifies which records from source (like a where keyword) =
                 "Field1 = " & [TableA].[Field1] = If tableA's field1 was 11 then the result would be "Field1 = 11".  If the field was alpha you would also have to concatenate quotes into the criteria.  See the help example:

intX = DCount("[ShippedDate]", "Orders", "[ShipCountry] = '" _
& Forms!Orders![ShipCountry] & "'AND [ShippedDate] > #1-1-95#")

I hope that clarifies it some.  Let me know if it doesn't.  Just glad I could help, kkirt1!

lmerrell
0
 
kkirt1Author Commented:
Edited text of question.
0
 
afpcosCommented:
SELECT table1.field1,Count(table2.field1),Count(table3.field1)
FROM table1, OUTER table2, OUTER table3
WHERE table1.field1 = table2.field1
AND tablet.field1 = table3.field1
GROUP BY table1.field1

You need the outer joins to insure a count if the other table does not have any relations.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
KangaRooCommented:
Too late! BTW, you don't need joins here:
SELECT
  Tbl1.Fld1
  , COUNT(1) as TBL1_Count
  , (SELECT COUNT(1) FROM TBL2 WHERE TBL2.FldRef1 = Tbl1.Fld1) as TBL2_Count
  , (SELECT COUNT(1) FROM TBL3 WHERE TBL3.FldRef1 = Tbl1.Fld1) as TBL3_Count
FROM Tbl1
GROUP BY Tbl1.Fld1;


0
 
lmerrellCommented:
This will do it for you, kkirt1:

SELECT TableA.Field1, DCount("Field2","TableB","Field1 = " & [TableA].[Field1]) AS [Count of TableB], DCount("Field3","TableC","Field1 = " & [TableA].[Field1]) AS [Count of TableC]
FROM TableA
GROUP BY TableA.Field1, DCount("Field2","TableB","Field1 = " & [TableA].[Field1]), DCount("Field3","TableC","Field1 = " & [TableA].[Field1]);


lmerrell
0
 
setsCommented:
kkirt1,

Your requirement cannot be met through one select sql.

one Select SQL,  is mainly to select information from one main table only.  However,  if the main table is related to other subset tables,  you could get information from subset tables using the relation field.

In your scenario,
you have three tables.
t1 , t2 , t3.

through your texts,  I understand,  the relation however exists like this.
       t1--t2
        \  /
         t3

By your requirement,  you seem to read number of records in t1, t2 and t3 without much usage of the relationship.  I donot understand,  where the picture of group by fits in.

The easy way to do is execute three seperate queries.  

Select count(*) as T1count from t1
Select count(*) as T2count from t2
Select count(*) as T3count from t3

Since,  you wanted these results in one record,  you must analyze what is the condition which binds these three queries to be used to relate and form one record.

I hope you understand, and tackle your issues in different angle.

0
 
KangaRooCommented:
In fact, the group by is only neccesary if Tbl1.Fld1 can contain duplicate entries, which it doesn't:
SELECT
   Tbl1.Fld1
   , 1 as Tbl1_Count
   , (SELECT COUNT(1) FROM TBL2 WHERE TBL2.FldRef1 = Tbl1.Fld1) as TBL2_Count
   , (SELECT COUNT(1) FROM TBL3 WHERE TBL3.FldRef1 = Tbl1.Fld1) as TBL3_Count
FROM Tbl1;

0
 
lmerrellCommented:
Here a little cleaner.  And no joins here either.  And no additional Selects:

SELECT TableA.Field1,
DCount("Field2","TableB","Field1 = " & [TableA].[Field1]) AS [Count of TableB], DCount("Field3","TableC","Field1 = " & [TableA].[Field1]) AS [Count of TableC]
FROM TableA
GROUP BY TableA.Field1;

lmerrell

0
 
lmerrellCommented:
Same here, KangaRoo!  ;-)

SELECT TableA.Field1,
DCount("Field2","TableB","Field1 = " & [TableA].[Field1]) AS [Count of TableB],

DCount("Field3","TableC","Field1 = " & [TableA].[Field1]) AS [Count of TableC]
FROM TableA;

lmerrell
0
 
lmerrellCommented:
sets - As you can see, "There is more than one way to skin any cat" ;-)

lmerrell
0
 
KangaRooCommented:
:)
0
 
kkirt1Author Commented:
Sorry afpcos, it eliminted the duplicate records but it still comes back with a count of 2 for the records in table 2.  I need it to return the number 1 (the number of records in table 2).


0
 
KangaRooCommented:
Have yo tried Imererell's and my solution yet?
0
 
lmerrellCommented:
I'm sure that both approaches will work.  I'm curious as to which would be more efficient.  Select statement vs. Function?  What do you think KangaRoo.
0
 
KangaRooCommented:
Might depend on the selection fields being indexed. Does DCount use indexed or sequential selection?

The online on DCount remarks:

Tip   The Count function has been optimized to speed counting of records in queries. Use the Count function in a query expression instead of the DCount function, and set optional criteria to enforce any restrictions on the results. Use the DCount function when you must count records in a domain from within a code module or macro, or in a calculated control.
0
 
lmerrellCommented:
Interesting.  I would definitely use Count when trying to obtain counts of records pertaining to the Selected datasource, but my use of DCount acts more like scenario of use with a calculated control.  I might try testing with larger volumes of data - when I find the time, of course! ;-)
0
 
lmerrellCommented:
Access help does state:

Use the DCount function in Visual Basic, a macro, a query expression, or a calculated control.

and

You can use the DCount function to count the number of records containing a particular field that isn't in the record source on which your form or report.  (It says form or report but the principle should be the same - to count the number of records containing a particular field that isn't in the record source.

0
 
lmerrellCommented:
Oops!  Left "is based" off of "on which your form or report".  I misscut!  ;-)
0
 
KangaRooCommented:
Testing would be the idea. Tomorrow.
0
 
jason_lewisCommented:
Here's yet another approach. (One more way to 'skin the cat') It's not strict SQL but it will work in Access. (I'm assuming that this is in Access?)

In Access, you can use the immediate if function (IIf) inside your SQL. So instead of counting, test for null, and add one to the total if it is not null:

SELECT Field1, Sum(Iif(Table2.Field2 is NULL, 0, 1)) as Table2Count, Sum(Iif(Table3.Field2 is NULL, 0, 1)) as Table3.Count
FROM (Table1 LEFT JOIN Table 2 on Table1.Field1 = Table2.Field1) LEFT JOIN Table3 on Table1.Field1 = Table3Field3

By the way -- If Kangaroo's Subquery idea (9:46 am) works, it will probably be the most efficient. Mine combines BOTH joins and functions, which both slow things down. (I guess I only posted it because I'm positive it will work -- if nothing else does.)
0
 
kkirt1Author Commented:
Yes, the dcount would work if table one only had one record (I guess that is what I said orginally to make things simpler - sorry)  

The tables actually have multiple records.  Maybe I can clarify: I want to count the number of records that relate within the three tables.  Table one relates to table two which relates to table 3.  So table one may have 10 records, table two may have  100 records and table 3 may have 1000 records.  When all is said and done the SQL statement should come back with 10 records and the count of records that match each record from table two and so on.

Table1,Field 1......Table2,Field 2.....Table3,Field2
.....1..................10................50
.....2..................20................25



The total for the Table2,Field 2 column should be the total number of records in that table.  The total for Table 3,Field 2 column should be the total number of records in that table.  

The counts with a simple sql count function are coming back with more records than are in the tables.  The records in table2 are being counted more than once.

I tried to make it as simple as possible and in doing so I wasted a lot of your time.  Sorry!  

Any ideas you have would be appreciated.
0
 
KangaRooCommented:
Did you actually test Immerell's and mine proposal?
0
 
kkirt1Author Commented:
Yes, it worked to bring back the total number of records from table2 and table3.  I need to either expand on that solution or go a diferent direction.  

I tried using the Dcount function to bring back only the count from table2 that related to a record in table1 but couldn't get it.  It either came back with the count of all the records or a none.
0
 
lmerrellCommented:
kkirt1,

I think we understood your requirements.  I just ran a test with multiple records in TableA - 3 records; TableB - 6 records; TableC - 12 records.

SELECT TableA.Field1,
DCount("Field2","TableB","Field1 = " & [TableA].[Field1])
AS [Count of TableB],
DCount("Field3","TableC","Field1 = " & [TableA].[Field1])
AS [Count of TableC]
FROM TableA;

Resulting output:

Field1      Count of TableB      Count of TableC
11                            1                            2
22                            2                            4
33                            3                            6

My TableA records were 11, 22, 33.
My TableB records were 11-a, 22-a, 22-b, 33-a, 33-b, 33-c.
My TableC records were 11-a, 11-b, 22-a, 22-b, 22-c, 22-d, 33-a, 33-b, 33-c, 33-d, 33-e, 33-f.

with 11-a being Field1 11; Field2 a for example.

It seems to work fine for me.  Or am I missing the point. ;-)

lmerrell
0
 
lmerrellCommented:
It is key to use the third argument of the DCount function to match the key field of TableA to the value of the key field from TableA.  Of course my TableA is your Table1, etc.
0
 
KangaRooCommented:
The same applies to the WHERE clauses in the subqueries. Can you gave us the actual names of the tables and the relevant fieldnames and the relations?
I'm unclear on one poit. In the Q you stated that table two and three have a field that refers to table one. A few comments back you say that table three refers to table two and table two refers to table one.
0
 
kkirt1Author Commented:
Sorry KangaRoo.  I have the tables set up both ways - just trying to get something to work.  Ideally table three would not have to have a field that relates it to table1.  It would use the table2 relationship.  

Immerell:  I am working on implementing your solution.  It looks promissing.  I will let you know.  Thanks!
0
 
KangaRooCommented:
Can you give the tables, fields and relations. Table 3 depending on Table 2 is rather a big change. For both methods it means that the columns depend on each other, now both counts only depend on Table 1
0
 
kkirt1Author Commented:
lmerrell:  I think that is it.  A thousand thanks (oh, and the points too).  One last question though.  I have been messing with the dcount function and cannot figure out the syntax.  Actually I am trying to add another criteria and the " and the & is very confusing.  Microsoft's help is no help!  Can you help me understand that syntax?  Thanks!
0
 
kkirt1Author Commented:
What about the Dcount syntax?  Any help, or have I used up my 100 points?  Either way, Thanks!
0
 
lmerrellCommented:
kkirt1,

Check the text of my answer.  Up at the top of the question thread.  Just click on "Text above...".  I tried to explain the syntax there.  Let me know if you need more help with it. ;-)

lmerrell
0
All Courses

From novice to tech pro — start learning today.