G
Guest
Hi,
I created a query with the same table twice. They include different data but
the ones I am interested in are the date and status (status being in or out
of the water and coded as 1 or 2). Anyway, as I mentioned I created a query
with the same table twice, where using a line of code in SQL I requested
machine#;deployment date; retrieval date; today..... got the deployment dates
and retrieval dates using a combo of SELECT and WHERE. At first I obtained a
table with all possible combination of deployment dates and retrieval dates
without any concern which retrieval was from what deployment. I added a
condition as retrieval date >= deployment date and that took care of part of
the problem, as you have to put something in before taking it out. But I am
still having way too many records because my query would still give me all
possible combo of deployment dates and posterior retrieval dates (includig
retrieval dates happening way later as part of other deployments):
ex: deployment dates - retrieval dates
6/7/06 - 6/30/06
6/7/06 - 8/9/06 --> that one being wrong!
8/1/06 - 8/9/06
So my question: I was trying to incorporate a criteria (like the "has to be
larger or equal") to tell the query that for each machine the deployment date
has to be UNIQUE. I look in the Access reference books that I have but
couldn't find what I am looking for.
here is what I got so far:
SELECT [Deployments].SERIAL, [Deployments].DATE AS Deploy,
[Deployments_1].DATE AS Retrieve, Now() AS Today, [Deployments].NOTES AS
Battery, [Deployments_1].NOTES
FROM[Deployments] INNER JOIN [Deployments] AS [Deployments_1] ON
[Deployments].SERIAL= [Deployments_1].SERIAL
WHERE ((([Deployments_1].DATE)>=[Deployments].DATE) And
(([Deployments].STATUS)=1) And (([Deployments_1].STATUS)=2));
I could work around this problem by creating another query after that to
clean it up, but I was curious to know if there was a trick to do that all at
once?
Thanks for the help.
Marie.
I created a query with the same table twice. They include different data but
the ones I am interested in are the date and status (status being in or out
of the water and coded as 1 or 2). Anyway, as I mentioned I created a query
with the same table twice, where using a line of code in SQL I requested
machine#;deployment date; retrieval date; today..... got the deployment dates
and retrieval dates using a combo of SELECT and WHERE. At first I obtained a
table with all possible combination of deployment dates and retrieval dates
without any concern which retrieval was from what deployment. I added a
condition as retrieval date >= deployment date and that took care of part of
the problem, as you have to put something in before taking it out. But I am
still having way too many records because my query would still give me all
possible combo of deployment dates and posterior retrieval dates (includig
retrieval dates happening way later as part of other deployments):
ex: deployment dates - retrieval dates
6/7/06 - 6/30/06
6/7/06 - 8/9/06 --> that one being wrong!
8/1/06 - 8/9/06
So my question: I was trying to incorporate a criteria (like the "has to be
larger or equal") to tell the query that for each machine the deployment date
has to be UNIQUE. I look in the Access reference books that I have but
couldn't find what I am looking for.
here is what I got so far:
SELECT [Deployments].SERIAL, [Deployments].DATE AS Deploy,
[Deployments_1].DATE AS Retrieve, Now() AS Today, [Deployments].NOTES AS
Battery, [Deployments_1].NOTES
FROM[Deployments] INNER JOIN [Deployments] AS [Deployments_1] ON
[Deployments].SERIAL= [Deployments_1].SERIAL
WHERE ((([Deployments_1].DATE)>=[Deployments].DATE) And
(([Deployments].STATUS)=1) And (([Deployments_1].STATUS)=2));
I could work around this problem by creating another query after that to
clean it up, but I was curious to know if there was a trick to do that all at
once?
Thanks for the help.
Marie.