query with twice the same table, and the trouble!

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
In query design mode - go to View in the menu bar and select Properties. You
will find an entry for Unique Values with the default at No...change to yes

(be sure your cursor is not active in any field when you open Properties as
otherwise it will give you only the properties of that field....)
 
Thank you. I just tried that but it didn't change anything; I am still
getting wrong combinations of dates... Any other suggestions?

NetworkTrade said:
In query design mode - go to View in the menu bar and select Properties. You
will find an entry for Unique Values with the default at No...change to yes

(be sure your cursor is not active in any field when you open Properties as
otherwise it will give you only the properties of that field....)
--
NTC


Pretty_please said:
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.
Click to expand...
Click to expand...
 
ok - well I mainly focused on your emphasis of needing a UNIQUE value...

am not able to determine your need - sorry - - all one can say though is
that the query is never "wrong" it is always right...just returning
"unexpected" results...

one note; you have "the same table twice...with different data" seems like
a contradiction in terms.

you might repost a new question stripped down to just a mini sample of Table
X and mini sample of your needed result....perhaps others will relook
--
NTC


Pretty_please said:
Thank you. I just tried that but it didn't change anything; I am still
getting wrong combinations of dates... Any other suggestions?

NetworkTrade said:
In query design mode - go to View in the menu bar and select Properties. You
will find an entry for Unique Values with the default at No...change to yes

(be sure your cursor is not active in any field when you open Properties as
otherwise it will give you only the properties of that field....)
--
NTC


Pretty_please said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top