0

enter image description hereenter image description hereI have three Access tables. I tried to make a query depend on date, but the query does not return any value.

SELECT Sum(income2.mony) AS SumOfmony, Sum(outcome.mony) AS SumOfmony1, Sum(PT.PT_Paid) AS SumOfPT_Paid, Sum(PT.PT_Remain) AS SumOfPT_Remain, PT.PT_Date
FROM (PT INNER JOIN income2 ON PT.PT_Date = income2.dat) INNER JOIN outcome ON PT.PT_Date = outcome.[dat&time]
GROUP BY PT.PT_Date;

enter image description here

Makky Mohamad
  • 1
  • 2
  • Can you add the data that is in the tables and what you expect the outcome of the query to be, please? Without seeing the data, no-one will be able to make a helpful suggestion other than "Check the PT_Date/Dat/Dat&Time values and make sure they match" – JayV Aug 29 '18 at 20:09
  • type of data are the same in feild (dat,dat&time&Pt_Date) is date and time format – Makky Mohamad Aug 29 '18 at 20:39
  • Your question's tag is incorrect. Please, fix them so that people wouldn't confuse. – JohnyL Aug 29 '18 at 20:45
  • https://drive.google.com/open?id=1BooHc9XZauMh-TXLz0CtnweN9_YdrnVx – Makky Mohamad Aug 29 '18 at 20:45
  • please show some data. my guess is that the time section in these fields contains non-identical values, and therefor do not join. – marlan Aug 29 '18 at 22:05
  • yes marian there is unidentical dates.but i think it should to give me zero.if i removed the joins it giving me values – Makky Mohamad Aug 29 '18 at 22:12

1 Answers1

0

Seems like you want subqueries, not joins. INNER JOIN returns no records on unmatched results.

Then, there's an additional problem: dates can store fractions of seconds. Comparing dates for equality leads to trouble in Access (see this for an example)

Try querying your external tables using these subqueries:

SELECT Sum(income2.mony) FROM income2 WHERE DateDiff("s",PT.PT_Date, income2.dat) = 0
SELECT Sum(outcome.mony) FROM outcome WHERE DateDiff("s",PT.PT_Date, outcome.[dat&time]) = 0

Implemented:

SELECT
    (SELECT Sum(income2.mony) FROM income2 WHERE DateDiff("s",PT.PT_Date, income2.dat) = 0) As SumOfmony,
    (SELECT Sum(outcome.mony) FROM outcome WHERE DateDiff("s",PT.PT_Date, outcome.[dat&time]) = 0) AS SumOfmony1,
    Sum(PT.PT_Paid) AS SumOfPT_Paid, Sum(PT.PT_Remain) AS SumOfPT_Remain, PT.PT_Dat
FROM PT
GROUP BY PT.PT_Date;
Erik A
  • 31,639
  • 12
  • 42
  • 67