MS Access- Query not filtering correct records

I have a query that filter records with recent time stamp. The query filter recent records but it's filtering other records also. For example: it should diplay recent record from 2000-2016, it filter recent record and also it's filtering records from 2012 to 2015 like not displaying these records although these should be display with the recent timestamp.Below is the sql:

SELECT t.ID, t.Country, t.Product, t.Years, t.Values, t.Show, t.Current, t.Source, t.Notes, t.Entered_By, t.Timestamp, t.Sector, t.PType, t.Flow, t.Unit, t.DataType
FROM qryUnion AS t INNER JOIN (SELECT [qryUnion].Country, [qryUnion].Product, [qryUnion].Years, [qryUnion].Flow, [qryUnion].DataType, MAX([qryUnion].Timestamp) AS MaxofTimestamp FROM qryUnion GROUP BY [qryUnion].Country, [qryUnion].Product, [qryUnion].Years,[qryUnion].Flow, [qryUnion].DataType)  AS q ON (t.Timestamp = q.MaxofTimestamp) AND (t.Flow = q.Flow) AND (t.Years = q.Years) AND (t.Product = q.Product) AND (t.Country = q.Country) AND (t.DataType = q.DataType);

i try to figure out the error, i guess it's for timestamp which i am not able to figure out how.


Any help?

Thank you
LVL 1
WSStudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Query seems correct, so probably data are not as you expect them to be.
As a start, replace this:

(t.Timestamp = q.MaxofTimestamp) 

Open in new window

with:

(DateDiff("s", t.Timestamp, MaxofTimestamp) =0)

Open in new window

0
WSStudentAuthor Commented:
@Gustav, it says "JOIN expression not supported"
...
The query was working find two days back but then all of a sudden when new data comes in it stop filtering properly. Although it's filter for some years but not for other. I try running "Compact and Repair" but it's not working at all.
...
Also as you can see in union query image result there is values for 2012,2013,2014,2015 but in recent query there is no record for that , although it should be there.
recent-query-result.png
union-query-result.png
0
WSStudentAuthor Commented:
i change " MaxofTimestamp" to ..
(DateDiff("s", t.Timestamp, q.MaxofTimestamp) =0)

Open in new window

, but this is also not filtering correct result.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Gustav BrockCIOCommented:
Then remove it from the Inner Join and add a Where clause:

Where DateDiff("s", t.Timestamp, MaxofTimestamp) =0

Open in new window

1
WSStudentAuthor Commented:
Changing "MaxofTimestamp" to "q.MaxofTimestamp" make the first code run but again it's filtering the same incorrect data.
...
Using this Where also gives the same incorrect result.
0
Gustav BrockCIOCommented:
Then, I guess, your subquery:

(SELECT [qryUnion].Country, [qryUnion].Product, [qryUnion].Years, [qryUnion].Flow, [qryUnion].DataType, MAX([qryUnion].Timestamp) AS MaxofTimestamp FROM qryUnion GROUP BY [qryUnion].Country, [qryUnion].Product, [qryUnion].Years,[qryUnion].Flow, [qryUnion].DataType)  AS q

Open in new window

returns a different (later) MaxofTimestamp than you expect.

Doublecheck the output of this query.
0
WSStudentAuthor Commented:
still the same incorrect filtering ,data from 2012 to 2015 is missing.
...
Union query retuens all the records so it's okay till union query and tables are also giving out result. It's just this recent query which doesn't display/filter correct result.
0
Gustav BrockCIOCommented:
It can't be.

If the records exist in the union query, so they will if all you do is to use Max on TimeStamp and Group By on all other fields except ID.
0
WSStudentAuthor Commented:
any idea on how to fix this? i am not able to understand the reason behind this error.
0
WSStudentAuthor Commented:
i figure out that , as my database id divided into front and backend, when i connect with one version of backend the original one it's not woking/filtering but when i connect with the different one the query works, any idea why?
0
aikimarkCommented:
Please try this
SELECT t.ID, t.Country, t.Product, t.Years, t.Values, t.Show, 
t.Current, t.Source, t.Notes, t.Entered_By, t.Timestamp, t.Sector, 
t.PType, t.Flow, t.Unit, t.DataType

FROM qryUnion AS t INNER JOIN 

(SELECT Country, Product, Years, Flow, DataType, 
MAX(Timestamp) AS MaxofTimestamp 
FROM qryUnion 
GROUP BY Country, Product, Years, Flow, DataType)  AS q 

ON (t.Country = q.Country) AND (t.Product = q.Product) AND (t.Years = q.Years) AND (t.Flow = q.Flow) AND (t.DataType = q.DataType) 
AND (t.Timestamp = q.MaxofTimestamp);

Open in new window

1
WSStudentAuthor Commented:
@aikimark, still the same issue, not filtering correct data.
....
Also i notice one more thing, if i change year it start working, why is that so , any idea? but changing year is not the way as the data have specific year, but what the year issue is in sql ?
0
John TsioumprisSoftware & Systems EngineerCommented:
Have you tried Compact and Repair?
0
aikimarkCommented:
if i change year it start working
What do you mean by this?
0
WSStudentAuthor Commented:
yeah tried that many time , that's not working.
0
aikimarkCommented:
Now, please test this version.
SELECT t.ID, t.Country, t.Product, t.Years, t.Values, t.Show, 
t.Current, t.Source, t.Notes, t.Entered_By, t.Timestamp, t.Sector, 
t.PType, t.Flow, t.Unit, t.DataType

FROM qryUnion AS t INNER JOIN 

(SELECT Country, Product, Years, Flow, DataType, 
MAX(Timestamp) AS MaxofTimestamp 
FROM qryUnion 
GROUP BY Country, Product, Years, Flow, DataType)  AS q 

ON (t.Country = q.Country) AND (t.Product = q.Product) AND (t.Years = q.Years) 
AND (t.Flow = q.Flow) AND (t.DataType = q.DataType) 

WHERE (t.Timestamp = q.MaxofTimestamp)

Open in new window

1
WSStudentAuthor Commented:
@aikimark, i was trying to figure out changing what criteria would let the correct data to show up, so when i change year field data like change 2012 to 2020 , it start showing up in recent query, but if i change 2012 to 2013 that it won'e show up like data from 2012 to 2015 is not filtering , i can't understand why?
0
WSStudentAuthor Commented:
@aikimark, not working , still the same, data from 2012 to 2015 not showing up.

...
union-query-result.png
recent-query-result.png
0
Gustav BrockCIOCommented:
when i connect with one version of backend the original one it's not woking/filtering but when i connect with the different one the query works, any idea why?
Yes, then the records differ. Obviously.

There isn't anything else to do than to study the data carefully to find out how they differ. We can't do that for you.
1
WSStudentAuthor Commented:
@Gustav, i read the data completely. infact i try to compare in excel and other tool but the data format is the same , and they are unique record so they should be displayed. As you can see in image also. data from 2000 to 2017 have same format and type. data from 2000 to 2011 is shown up but again not for 2012 to 2015.
0
Gustav BrockCIOCommented:
Yes, I see that.
But none of your queries can make data vanish. And if it works with one source and not with another, the data is what to research.
0
WSStudentAuthor Commented:
what about criteria? can that does (like vanish data)? like i said if i change year to 2020 it starts showing that data, but when i change to 2012 to 2015 it doesn't, what about that criteria?
0
aikimarkCommented:
Please post the qryUnion SQL and start preparing to post representative data from the tables that are referenced by qryUnion.
0
WSStudentAuthor Commented:
This is the union query ...
SELECT ID,Country,Product,Years,Values,Show,Curr,Source,Notes,Entered_By,Timestamp,Sec,Type,Flow,Unit,DataType 
From tblBal
UNION ALL  
SELECT ID,Country,Product,Years,Values,Show,Curr,Source,Notes,Entered_By,Timestamp,Sec,Type,"","",DataType 
From tblCap 
UNION all
SELECT ID,Country,Product,Years,Values,Show,Curr,Source,Notes,Entered_By,Timestamp,Sec,Type,"","",DataType 
From tblGen
UNION all
SELECT ID,Country,Product,Years,Values,Show,Curr,Source,Notes,Entered_By,Timestamp,Sec,Type,"","",DataType 
From tblHGen
UNION ALL 
SELECT ID,Country,Product,Years,Values,Show,Curr,Source,Notes,Entered_By,Timestamp,Sec,"","","",DataType 
From tblS;

Open in new window


....
Attach is the reference sheet for data. It's the Union Data,have to filter recent data from this using the above SQL Recent query.
test-Union-data.xlsx
0
aikimarkCommented:
Two of your join-criteria columns have an empty string value.
One of those columns is also used in the Group By clause.
0
aikimarkCommented:
Are these attached tables?  If so, refresh your tabledefs collection in the immediate window:
dbengine(0)(0).tabldefs.refresh

Open in new window

0
WSStudentAuthor Commented:
Union is for 5 table but the data which i am trying to apply recent is just for one table so that won't have much effect on that. As you can also see in the sheet the datatype is same so the data for the time being is just on one table , and even if's that the case for empty string that why is it working for 2000 to 2011 again.
0
WSStudentAuthor Commented:
these are 5 different table, did union and then filtering out data from them to export. I did refresh link tables but still the same.
0
aikimarkCommented:
Please post data from these tables.  Preferably, this will be in an Access database format so we don't have to jump through any extra hoops to find the problem.
0
Dale FyeCommented:
Where is this data coming from?  If it is coming from SQL Server and you are using a field with a datatype of Timestamp (sometimes called RowVersion), then you misunderstand the purpose of this data type.  If, on the other hand you have named the field [Timestam] and it is actually a date/time field, then querying the timestamp field as you have indicated is appropriate.

Dale
1
PatHartmanCommented:
As Dale said, the data type named TimeStamp in SQL Server is binary data and NOT a date/time as you think.  Here's a picture of a table with a timeStamp data field.  If you want to know the the most receintly entered record and you are using an Identity column or autonumber that is assigned sequentially, you can do a Max() on that column.  Otherwise, you will need to add a new column defined as DateTime and populate it with Now() as you add the row.
TimeStamp.JPG
1
Gustav BrockCIOCommented:
Again:
when i connect with one version of backend the original one it's not woking/filtering but when i connect with the different one the query works, any idea why?

Somehow the second backend is different from the first, and if the query works with the second backend, your issue is not with the query but with the first backend. You'll have to sort that out yourself as we don't have the data.
0
WSStudentAuthor Commented:
@Dale,@PatHartman, Timestamp field is Data/Time in my table. Attach is the image also.
Timestamp.png
0
WSStudentAuthor Commented:
It's quit strange to me as i never have this issue before, like filter for some records and vanishing other. It's the backend issue , something is wrong with either table/data. I don't think data as it's the same format and all and did analysis also on that. The year field is the one that doesn't let filter again i don't understand why, as year field is just number.
0
Gustav BrockCIOCommented:
That part is easy. Just study the two tables in Design view and look for differences.
1
WSStudentAuthor Commented:
So here the Solution is , it was a problem in SQL Query , the criteria was not well defined. Data was completely ok, as there was missing criteria (not filtering on one field) in Group by clause which was resulting in incorrect filtering.

But thank you everyone for your help and guidance. All of your answers really help me in reaching my solution.

Thanks again.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WSStudentAuthor Commented:
My answer is the solution that is incorrect criteria.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.