Query from 2 DB is retrieving bad information

[GHawk]

Hi I have this query request from 2 tables: Tbl1 and Tbl2.

I'm trying to get total working hours per day from Tbl1 (SO) and used hours from Tbl2 (Hours) on the same day. And I want to display totals per day

SELECT (Count(SO)*8.5) as Working, Round(Sum(Hours),2) as Used, INT(Datein) as Day FROM Tbl1,
Tbl2 WHERE (INT(Datein) = Int(Fecha)) GROUP BY INT(Datein),INT(Fecha)

I run this and is getting thru but results I'm getting are bad on both Working and Used.

One more thing I'm not doing a join because I used to get the "Join Expression Not Supported" error.

Thanks in advance for your support.

[aytee111]

This is called mixing and matching, who knows what results will be displayed. Always fix problems, error messages are given to you for a reason.

I would recommend doing this in multiple queries - one for each table and then one to JOIN the two resulting queries, with a join that works. That will simplify what your results are showing and will be much easier to troubleshoot.

[ssanfu]

SELECT (Count(SO)*8.5) as Working, Round(Sum(Hours),2) as Used, INT(Datein) as Day 
FROM Tbl1, Tbl2 
WHERE (INT(Datein) = Int(Fecha)) GROUP BY INT(Datein),INT(Fecha)

This results in a Cartesian product or Cartesian join.
A Cartesian join is when you join every row of one table to every row of another table. Most of the time, not very useful.

Can't you link the tables on "Fecha"?

[GHawk]

Hi, thanks for the quick answer
aytee111: I know errors are there to be cleaned but that particular error has no clear answer and I spent too much time on it. I'm not an expert so if you can explain in detail how the multiple queries would look like I will appreciate it.

ssanfuL: I tried this:
"SELECT (Count(SO)) as Working, Round(Sum(Hours),2) as Used, INT(Datein) as Day FROM Tbl1 LEFT JOIN Tbl2 ON (INT(Datein) = Int(Fecha)) GROUP BY INT(Datein),INT(Fecha)"

But I got constantly a JOIN Expression Not Supported. I will appreciate if you can look there is something wrong here....

[June7]

What is Fecha field?

Why are you using Int() function?

If these tables do not have a relationship then can't join in query.

aytee might be suggesting you do two aggregate queries, one for each table with grouping on the common linking field. Then build a query that joins the two aggregate queries on the common field. This will work only if both tables have the same values in the common field. Otherwise need a dataset of all possible common values and link the two aggregate queries to that dataset.

[ssanfu]

<snip>..LEFT JOIN Tbl2 ON (INT(Datein) = Int(Fecha))..<snip>

Why are you using the INT() function on what I am guessing are date fields?

[GHawk]

June7 , Fecha is a Date/Time field. I'm using the INT to get the Date part only (like short date) that way I can join both tables. On regards of Aytee's suggestion I agree but I'm just not savvy enough to make that query, I appreciate any help I can get.

ssanfu, as I just said I'm taking the Date part only of a long date field (Or at least that is what I'm trying to do) maybe that the key part of my problem... Is there any other way to get the Date part only?...

[ssanfu]

I prefer to use the DateValue() function.

If the date argument includes time information, DateValue doesn't return it. However, if date includes invalid time information (such as "89:98"), an error occurs

The Int() function rounds numbers..... while Int() does remove the decimal part (the time), it does round.

There are also the conversions functions like Clng(). It would look like:

CDate(CDbl(CLng(CDbl([datetimefield]))))

It takes the date/time field, converts it to a double, converts the result to a Long Int, converts that result back to a double, finally converts the result back to a date/time.

[GHawk]

ssanfu I change that INT to DateValue but I'm still having the cartesian issue, I really need some help here to get only the information I need as I'm clueless on the suggestions about how to make the other query...

[June7]

What specifically do you not understand about suggestion described in post 5?

If you want to provide db for analysis, follow instructions at bottom of my post.

[GHawk]

June7, I mean I can make the individual query for each table grouped by the Date field. But I don't know how to create the extra query to join them. (I think I tried by using the Join query I put on post #4 which used to give me an error)

[June7]

The query should be simple join on the common field the queries are grouped by. The query in post 4 is joining tables, not 2 aggregate queries.

[GHawk]

June7, thanks for clarifying that (as I mentioned before I'm not very good at this) I change that but on the JOIN I'm still getting the original JOIN Expression Not Supported Error :-\

Here is the query I came with:
"SELECT * FROM (SELECT (Count(SO)) as Working, DateValue(DateIn) as Day FROM Checadas GROUP BY DateValue(DateIn)) LEFT JOIN
(SELECT Round(Sum(Horas_Usadas),2) as Used, DateValue(Fecha) as Day2 FROM SeguimientoOrdenes GROUP BY DateValue(Fecha)) ON
(Dia = Dia2)"

As an extra information: here is where I'm getting the error:
Dim DAx As New OleDb.OleDbDataAdapter(MySQLQuery, frm_kit.CNEff)
Dim DSx As New DataSet
' DAx.Fill(DSx, MyTable)
DAx.Fill(DSx) <---- Here is the error

[June7]

There are no fields in the subqueries named Dia and Dia2. There are Day and Day2.

The subqueries need alias names.

Did you use query designer to build and save query objects? Use query designer to help get correct SQL syntax if you want to build an all-in-one statement. The final query should be like:

SELECT Q1.*, Q2.* FROM (SELECT Count(SO) AS Working, DateValue(DateIn) AS Q1Day FROM Checadas GROUP BY DateValue(DateIn)) AS Q1 INNER JOIN
(SELECT Round(Sum(Horas_Usadas),2) as Used, DateValue(Fecha) AS Q2Day FROM SeguimientoOrdenes GROUP BY DateValue(Fecha)) AS Q2 ON
Q1.Q1Day=Q2.Q2Day;

[GHawk]

June7, about Day and dia it was a typo as I changed the names for the forum but forgot to change those ones. About the suggestion it was really helpful and I actually got the results that I need, thank you very much