Consecutive Date Query

[NeonSky via AccessMonster.com]

Good Day Everyone,

Got a bit of a tough one today that has had me stumped for the last few hours.
I hope someone can assist with this rather challenging question.

First please view my sample dataset as follows.

Name YearUsage ArrivalDate DepartDate Confo
Joe 2007 11/09/2007 11/16/2007 111
Joe 2007 11/16/2007 11/23/2007 222
Joe 2007 11/23/2007 11/30/2007 333
Joe 2007 12/21/2007 12/28/2007 444

What I would like to do is create a single record row for consecutive
reseravations. As indicated where Departure Dates match Arrival Dates. So our
final output should read as follows.

Name YearUsage ArrivalDate DepartDate Confo
Joe 2007 11/09/2007 11/30/2007 111,
222, 333
Joe 2007 12/21/2007 12/28/2007 444

FYI - Per the assistance of Marshal Walsh the concatenating portion of this
query can be performed by first creating another table that pulls all values
from the source table excluding the confo info...

SELECT Name, IIf(False," ",Null) AS Concat INTO tbldestination
FROM tblSource
GROUP BY Name, Address;

Next to perform the concatenation the following example illustrates this
concept....

UPDATE tbldestination INNER JOIN tblsource ON tbldestination.Name=tblsource.
Name SET tbldestination.concat = ([concat]+", ") & [source.confo]

Of course many thanks for your time and efforts!

 

[NeonSky via AccessMonster.com]

Correction that is "Michel" Walsh!

 

[Michel Walsh]

I see it as two problems to be solved.

The first one is to generate the "Arrival-Departure" match. To 'simplify'
the solution, I will use three queries for that:


Arrivals
----------------
SELECT a.Name, a.ArrivalDate
FROM Table2 AS a LEFT JOIN Table2 AS b
ON (a.ArrivalDate = b.DepartDate) AND (a.Name = b.Name)
WHERE (((b.Name) Is Null));



supply the ArrivalDate limits.


Departures
---------------
SELECT a.Name, a.DepartDate
FROM Table2 AS a LEFT JOIN Table2 AS b ON (a.DepartDate = b.ArrivalDate) AND
(a.Name = b.Name)
WHERE (((b.Name) Is Null));


supply the DeparDate limits.


ArrivalsDepartures
----------------
SELECT Arrivals.Name, Arrivals.ArrivalDate AS Arrival,
Min(Departures.DepartDate) AS Departure
FROM Arrivals INNER JOIN Departures ON Arrivals.Name = Departures.Name AND
Arrivals.ArrivalDate <= departures.departDate
GROUP BY Arrivals.Name, Arrivals.ArrivalDate;


supply the concatenation groups.



The first problem is solve. The second problem is the concatenation itself.


You can now create the temporary table, tempAD:
-------------------
SELECT ArrivalsDepartures.Name, ArrivalsDepartures.Arrival,
ArrivalsDepartures.Departure, IIf(False," ",Null) AS concat
INTO tempAD
FROM ArrivalsDepartures;

(NOTE: you have to delete the table tempAD if the table already exist,
before running that query)





And now, fill its concatenation field:
------------------
UPDATE tempAD INNER JOIN Table2
ON (tempAD.Arrival <= Table2.DepartDate)
AND (tempAD.Departure >= Table2.ArrivalDate)
AND (tempAD.Name = Table2.Name)
SET concat = (concat + ", ") & confo



So, in summary, make sure tempAD does not exist in your bd, run the make
table, then run the last query, an the result is in the table tempAD:

tempAD
Name Arrival Departure concat
Joe 2007.11.09 2007.11.30 333, 222, 111
Joe 2007.12.21 2007.12.28 444





Hoping it may help,
Vanderghast, Access MVP

Good Day Everyone,

Got a bit of a tough one today that has had me stumped for the last few
hours.
I hope someone can assist with this rather challenging question.

First please view my sample dataset as follows.

Name YearUsage ArrivalDate DepartDate Confo
Joe 2007 11/09/2007 11/16/2007
111
Joe 2007 11/16/2007 11/23/2007
222
Joe 2007 11/23/2007 11/30/2007
333
Joe 2007 12/21/2007 12/28/2007
444

What I would like to do is create a single record row for consecutive
reseravations. As indicated where Departure Dates match Arrival Dates. So
our
final output should read as follows.

Name YearUsage ArrivalDate DepartDate Confo
Joe 2007 11/09/2007 11/30/2007
111,
222, 333
Joe 2007 12/21/2007 12/28/2007
444

FYI - Per the assistance of Marshal Walsh the concatenating portion of
this
query can be performed by first creating another table that pulls all
values
from the source table excluding the confo info...

SELECT Name, IIf(False," ",Null) AS Concat INTO tbldestination
FROM tblSource
GROUP BY Name, Address;

Next to perform the concatenation the following example illustrates this
concept....

UPDATE tbldestination INNER JOIN tblsource ON
tbldestination.Name=tblsource.
Name SET tbldestination.concat = ([concat]+", ") & [source.confo]

Of course many thanks for your time and efforts!

 

[NeonSky via AccessMonster.com]

Michel, Absolutely top-notch crystal clear explanation that seamlessly
integrated into my process. I cant thank you enough. Happy Holidays!

I see it as two problems to be solved.

The first one is to generate the "Arrival-Departure" match. To 'simplify'
the solution, I will use three queries for that:

Arrivals
----------------
SELECT a.Name, a.ArrivalDate
FROM Table2 AS a LEFT JOIN Table2 AS b
ON (a.ArrivalDate = b.DepartDate) AND (a.Name = b.Name)
WHERE (((b.Name) Is Null));

supply the ArrivalDate limits.

Departures
---------------
SELECT a.Name, a.DepartDate
FROM Table2 AS a LEFT JOIN Table2 AS b ON (a.DepartDate = b.ArrivalDate) AND
(a.Name = b.Name)
WHERE (((b.Name) Is Null));

supply the DeparDate limits.

ArrivalsDepartures
----------------
SELECT Arrivals.Name, Arrivals.ArrivalDate AS Arrival,
Min(Departures.DepartDate) AS Departure
FROM Arrivals INNER JOIN Departures ON Arrivals.Name = Departures.Name AND
Arrivals.ArrivalDate <= departures.departDate
GROUP BY Arrivals.Name, Arrivals.ArrivalDate;

supply the concatenation groups.

The first problem is solve. The second problem is the concatenation itself.

You can now create the temporary table, tempAD:
-------------------
SELECT ArrivalsDepartures.Name, ArrivalsDepartures.Arrival,
ArrivalsDepartures.Departure, IIf(False," ",Null) AS concat
INTO tempAD
FROM ArrivalsDepartures;

(NOTE: you have to delete the table tempAD if the table already exist,
before running that query)

And now, fill its concatenation field:
------------------
UPDATE tempAD INNER JOIN Table2
ON (tempAD.Arrival <= Table2.DepartDate)
AND (tempAD.Departure >= Table2.ArrivalDate)
AND (tempAD.Name = Table2.Name)
SET concat = (concat + ", ") & confo

So, in summary, make sure tempAD does not exist in your bd, run the make
table, then run the last query, an the result is in the table tempAD:

tempAD
Name Arrival Departure concat
Joe 2007.11.09 2007.11.30 333, 222, 111
Joe 2007.12.21 2007.12.28 444

Hoping it may help,
Vanderghast, Access MVP

Good Day Everyone,

[quoted text clipped - 44 lines]

Of course many thanks for your time and efforts!

 

[NeonSky via AccessMonster.com]

Good Morning!

May I ask an additional question? What if I want to add additional fields to
the initial records set. For example...

Name YearUsage ArrivalDate DepartDate Confo
RType
Joe 2007 11/09/2007 11/16/2007 111
L
Joe 2007 11/09/2007 11/16/2007 345
O

As a result of th differing data in the RType I would like the process to
return two seperate records, though at present it is concatenating the two
records.

Thank you!

I see it as two problems to be solved.

The first one is to generate the "Arrival-Departure" match. To 'simplify'
the solution, I will use three queries for that:

Arrivals
----------------
SELECT a.Name, a.ArrivalDate
FROM Table2 AS a LEFT JOIN Table2 AS b
ON (a.ArrivalDate = b.DepartDate) AND (a.Name = b.Name)
WHERE (((b.Name) Is Null));

supply the ArrivalDate limits.

Departures
---------------
SELECT a.Name, a.DepartDate
FROM Table2 AS a LEFT JOIN Table2 AS b ON (a.DepartDate = b.ArrivalDate) AND
(a.Name = b.Name)
WHERE (((b.Name) Is Null));

supply the DeparDate limits.

ArrivalsDepartures
----------------
SELECT Arrivals.Name, Arrivals.ArrivalDate AS Arrival,
Min(Departures.DepartDate) AS Departure
FROM Arrivals INNER JOIN Departures ON Arrivals.Name = Departures.Name AND
Arrivals.ArrivalDate <= departures.departDate
GROUP BY Arrivals.Name, Arrivals.ArrivalDate;

supply the concatenation groups.

The first problem is solve. The second problem is the concatenation itself.

You can now create the temporary table, tempAD:
-------------------
SELECT ArrivalsDepartures.Name, ArrivalsDepartures.Arrival,
ArrivalsDepartures.Departure, IIf(False," ",Null) AS concat
INTO tempAD
FROM ArrivalsDepartures;

(NOTE: you have to delete the table tempAD if the table already exist,
before running that query)

And now, fill its concatenation field:
------------------
UPDATE tempAD INNER JOIN Table2
ON (tempAD.Arrival <= Table2.DepartDate)
AND (tempAD.Departure >= Table2.ArrivalDate)
AND (tempAD.Name = Table2.Name)
SET concat = (concat + ", ") & confo

So, in summary, make sure tempAD does not exist in your bd, run the make
table, then run the last query, an the result is in the table tempAD:

tempAD
Name Arrival Departure concat
Joe 2007.11.09 2007.11.30 333, 222, 111
Joe 2007.12.21 2007.12.28 444

Hoping it may help,
Vanderghast, Access MVP

Good Day Everyone,

[quoted text clipped - 44 lines]

Of course many thanks for your time and efforts!

 

[NeonSky via AccessMonster.com]

When posting the formating got a bit skewed, per my example the field added
to the initial set is as follows...

... Confo RType
... 111 L
... 345 O

Thanks!

Good Morning!

May I ask an additional question? What if I want to add additional fields to
the initial records set. For example...

Name YearUsage ArrivalDate DepartDate Confo
RType
Joe 2007 11/09/2007 11/16/2007 111
L
Joe 2007 11/09/2007 11/16/2007 345
O

As a result of th differing data in the RType I would like the process to
return two seperate records, though at present it is concatenating the two
records.

Thank you!

I see it as two problems to be solved.

[quoted text clipped - 65 lines]

 

[Michel Walsh]

In the first two queries, replace the table, Table2, by a saved query, let
us call it qd, which will be:


qd
------------------------------------------------
SELECT DISTINCT name, ArrivalDate, DepartDate
FROM yourOriginalTableNameHere



so

Arrivals
----------------
SELECT a.Name, a.ArrivalDate
FROM qd AS a LEFT JOIN qd AS b
ON (a.ArrivalDate = b.DepartDate) AND (a.Name = b.Name)
WHERE (((b.Name) Is Null));



supply the ArrivalDate limits.


Departures
---------------
SELECT a.Name, a.DepartDate
FROM qd AS a LEFT JOIN qd AS b ON (a.DepartDate = b.ArrivalDate) AND
(a.Name = b.Name)
WHERE (((b.Name) Is Null));





I have not fully checked the other queries, but it seems, at first glance,
they do not need to be modified.




Hoping it may help,
Vanderghast, Access MVP

When posting the formating got a bit skewed, per my example the field
added
to the initial set is as follows...

.. Confo RType
.. 111 L
.. 345 O

Thanks!

Good Morning!

May I ask an additional question? What if I want to add additional fields
to
the initial records set. For example...

Name YearUsage ArrivalDate DepartDate Confo
RType
Joe 2007 11/09/2007 11/16/2007
111
L
Joe 2007 11/09/2007 11/16/2007
345
O

As a result of th differing data in the RType I would like the process to
return two seperate records, though at present it is concatenating the two
records.

Thank you!

I see it as two problems to be solved.

[quoted text clipped - 65 lines]

Of course many thanks for your time and efforts!

 

[NeonSky via AccessMonster.com]

Hello Michel, How are you today? Thank you for your response. I created a
query that reflects what you have outlined below though it appears to not
achieve the desired output. Please notice the below breakdown...

____________________
Source Data

Name Address Arrival Departure Confo RType
Joe House2 11/1/2007 11/7/2007 111 O
Joe House2 11/1/2007 11/7/2007 333 L

____________________
Current Outupt

Name Address Arrival Departure Confo
Joe House2 11/1/2007 11/7/2007 111, 333

____________________

Desired Output

Name Address Arrival Departure Confo
Joe House2 11/1/2007 11/7/2007 111
Joe House2 11/1/2007 11/7/2007 333

Because the Arrival/Departure dates match it is combining the record, though
due to the differing RTYPE field I would like for them to remain in their own
distinct records.

Thank you for your consideration!

In the first two queries, replace the table, Table2, by a saved query, let
us call it qd, which will be:

qd
------------------------------------------------
SELECT DISTINCT name, ArrivalDate, DepartDate
FROM yourOriginalTableNameHere

so

Arrivals
----------------
SELECT a.Name, a.ArrivalDate
FROM qd AS a LEFT JOIN qd AS b
ON (a.ArrivalDate = b.DepartDate) AND (a.Name = b.Name)
WHERE (((b.Name) Is Null));

supply the ArrivalDate limits.

Departures
---------------
SELECT a.Name, a.DepartDate
FROM qd AS a LEFT JOIN qd AS b ON (a.DepartDate = b.ArrivalDate) AND
(a.Name = b.Name)
WHERE (((b.Name) Is Null));

I have not fully checked the other queries, but it seems, at first glance,
they do not need to be modified.

Hoping it may help,
Vanderghast, Access MVP

When posting the formating got a bit skewed, per my example the field
added

[quoted text clipped - 32 lines]

 

[NeonSky via AccessMonster.com]

Actually I think I sorted it out here on my end....Thanks though!

Hello Michel, How are you today? Thank you for your response. I created a
query that reflects what you have outlined below though it appears to not
achieve the desired output. Please notice the below breakdown...

____________________
Source Data

Name Address Arrival Departure Confo RType
Joe House2 11/1/2007 11/7/2007 111 O
Joe House2 11/1/2007 11/7/2007 333 L

____________________
Current Outupt

Name Address Arrival Departure Confo
Joe House2 11/1/2007 11/7/2007 111, 333

____________________

Desired Output

Name Address Arrival Departure Confo
Joe House2 11/1/2007 11/7/2007 111
Joe House2 11/1/2007 11/7/2007 333

Because the Arrival/Departure dates match it is combining the record, though
due to the differing RTYPE field I would like for them to remain in their own
distinct records.

Thank you for your consideration!

In the first two queries, replace the table, Table2, by a saved query, let
us call it qd, which will be:

[quoted text clipped - 33 lines]

 

[Michel Walsh]

It would have been a matter to consider that RType is the family name!
Indeed, to consider as if Joe O and Joe L would have been two different
users, rather than, actually, one user with two different resources. SQL
does not really know, neither really care, if RType is a resource, or a
family name, after all.

So, basically, to change:

a.name = b.name

to

a.name=b.name AND a.rtype=b.rtype


in the ON clauses, and, in the GROUP BY, to add rtype.




Hoping it may help,
Vanderghast, Access MVP

Actually I think I sorted it out here on my end....Thanks though!

Hello Michel, How are you today? Thank you for your response. I created a
query that reflects what you have outlined below though it appears to not
achieve the desired output. Please notice the below breakdown...

____________________
Source Data

Name Address Arrival Departure Confo RType
Joe House2 11/1/2007 11/7/2007 111 O
Joe House2 11/1/2007 11/7/2007 333 L

____________________
Current Outupt

Name Address Arrival Departure Confo
Joe House2 11/1/2007 11/7/2007 111, 333

____________________

Desired Output

Name Address Arrival Departure Confo
Joe House2 11/1/2007 11/7/2007 111
Joe House2 11/1/2007 11/7/2007 333

Because the Arrival/Departure dates match it is combining the record,
though
due to the differing RTYPE field I would like for them to remain in their
own
distinct records.

Thank you for your consideration!

In the first two queries, replace the table, Table2, by a saved query,
let
us call it qd, which will be:

[quoted text clipped - 33 lines]

Of course many thanks for your time and efforts!

 

[NeonSky via AccessMonster.com]

Hello Michel,

Thank you for additional clarification, I was able to apply what you outlined
and it worked great. Please note that per my descriptions and your excellent
analysis everything has come together and worked great so far. Though I have
another rather "interesting" addition to what I have previously outlined and
I was hoping you would help. If you have a moment please consider the below.

**These are the Source Records

Name Arrival Departure Code Confo
Joe 11/1/2007 11/7/2007 AB 5345
Joe 11/9/2007 11/30/2007 AB 999
Joe 11/16/2007 11/23/2007 AB 176
Joe 11/23/2007 11/23/2007 AB 43
Joe 11/23/2007 11/23/2007 AB 789
Joe 12/14/2007 12/21/2007 GV 456

**Per the Arrival Query previously outlined is generating the following
output....

Name Arrival Code
Joe 11/1/2007 AB
Joe 11/9/2007 AB
Joe 12/14/2007 GV

**Per the Departure Query previously outlined the following output is
generated...

Name Departure Code
Joe 11/7/2007 AB
Joe 11/30/2007 AB
Joe 11/30/2007 AB
Joe 12/21/2007 GV

**My temp/concat make table query generates...

Name Arrival Departure Code Concat
Joe 11/1/2007 11/7/2007 AB
Joe 11/9/2007 11/30/2007 AB
Joe 12/14/2007 12/21/2007 GV

**My final output is generating.....

Name Arrival Departure Code Concat
Joe 11/1/2007 11/7/2007 AB 5345
Joe 11/9/2007 11/30/2007 AB 43, 789, 176, 999
Joe 12/14/2007 12/21/2007 GV 456

****My desired output should read......

Name Arrival Departure Code Concat
Joe 11/1/2007 11/7/2007 AB 5345
Joe 11/9/2007 11/23/2007 AB 176, 999
Joe 11/23/2007 11/30/2007 AB 43, 789
Joe 12/14/2007 12/21/2007 GV 456

The key distinction is that where two "concurrent" date range records exist
that fall within the range of a "consecutive" date grouping these records
should recieve their own distinct groupings, and not be grouped within the
Consecutive range grouping.

Thank you for your time and consideration.

It would have been a matter to consider that RType is the family name!
Indeed, to consider as if Joe O and Joe L would have been two different
users, rather than, actually, one user with two different resources. SQL
does not really know, neither really care, if RType is a resource, or a
family name, after all.

So, basically, to change:

a.name = b.name

to

a.name=b.name AND a.rtype=b.rtype

in the ON clauses, and, in the GROUP BY, to add rtype.

Hoping it may help,
Vanderghast, Access MVP

Actually I think I sorted it out here on my end....Thanks though!

[quoted text clipped - 37 lines]

 

[Michel Walsh]

Consider the actual data:

Name Arrival Departure Code Confo
<a > Joe 11/9/2007 11/30/2007 AB 999
<b > Joe 11/16/2007 11/23/2007 AB 176
<c > Joe 11/23/2007 11/23/2007 AB 43
<d > Joe 11/23/2007 11/23/2007 AB 789


note that <a> has the same Name and same code than the other three records,
and that its arrival-departure include intervals in <b>, <c> and <d>. But
in the result, you want <a> to be concatenated with ONLY <b> and,
furthermore, you want a second group made of <c> and <d> concatenated
together, ***BUT*** the second concatenation group, made of <c> and <d>,
get its departure date changed for the one of <a> (which is NOT part of
that second group, again, made only of <c> and <d> ).


At least, I fail to see how the 'data' at hand (without any other outside
reference) can determine the desired output.

<a, b> Joe 11/9/2007 11/23/2007 AB 176, 999
<c, d> Joe 11/23/2007 11/30/2007 AB 43, 789


(and the logic has to deal with the departure of < a, b > becoming to the
MINIMUM of departures dates from <a> and <b>, as well as <c, d> getting the
MAXIMUM from <a, b> ??? )


Again, maybe there is some logic explaining it, with only the data at hand,
but I don't see it for the moment.


Vanderghast, Access MVP

 

[NeonSky via AccessMonster.com]

I rechecked my previous posts descrpiton and everything is as it should be,
outside of the source data. In light of this I can understand your confusion
as I made a most careless mistake in presenting my source data, it should
actually read as follows....I apologize for the mistake.

Name Arrival Departure Code Confo
Joe 11/1/2007 11/7/2007 AB 5345
Joe 11/9/2007 11/16/2007 AB 999
Joe 11/16/2007 11/23/2007 AB 176
Joe 11/23/2007 11/30/2007 AB 43
Joe 11/23/2007 11/30/2007 AB 789
Joe 12/14/2007 12/21/2007 GV 456

Consider the actual data:

Name Arrival Departure Code Confo
<a > Joe 11/9/2007 11/30/2007 AB 999
<b > Joe 11/16/2007 11/23/2007 AB 176
<c > Joe 11/23/2007 11/23/2007 AB 43
<d > Joe 11/23/2007 11/23/2007 AB 789

note that <a> has the same Name and same code than the other three records,
and that its arrival-departure include intervals in <b>, <c> and <d>. But
in the result, you want <a> to be concatenated with ONLY <b> and,
furthermore, you want a second group made of <c> and <d> concatenated
together, ***BUT*** the second concatenation group, made of <c> and <d>,
get its departure date changed for the one of <a> (which is NOT part of
that second group, again, made only of <c> and <d> ).

At least, I fail to see how the 'data' at hand (without any other outside
reference) can determine the desired output.

<a, b> Joe 11/9/2007 11/23/2007 AB 176, 999
<c, d> Joe 11/23/2007 11/30/2007 AB 43, 789

(and the logic has to deal with the departure of < a, b > becoming to the
MINIMUM of departures dates from <a> and <b>, as well as <c, d> getting the
MAXIMUM from <a, b> ??? )

Again, maybe there is some logic explaining it, with only the data at hand,
but I don't see it for the moment.

Vanderghast, Access MVP

Hello Michel,

[quoted text clipped - 65 lines]

Thank you for your time and consideration.

 

[Michel Walsh]

That sounds easier. A possible strategy is to add a computed field, like:


SELECT Name, arrival, departure, code, confo,
1 <> ( SELECT COUNT(*)
FROM originalTable As b
WHERE a.name =b.name
AND a.arrival=b.arrival
AND a.departure=b.departure
AND a.code=b.code ) AS IsConcurrent

FROM originalTable AS b



which should return:

Name Arrival Departure Code Confo IsConcurrent
Joe 11/1/2007 11/7/2007 AB 5345 0
Joe 11/9/2007 11/16/2007 AB 999 0
Joe 11/16/2007 11/23/2007 AB 176 0
Joe 11/23/2007 11/30/2007 AB 43 -1
Joe 11/23/2007 11/30/2007 AB 789 -1
Joe 12/14/2007 12/21/2007 GV 456 0



and now, the tri-ple { name, code, isConcurrent } is the new 'name' to
consider, or, if you prefer, { code, isConcurrent } is the new 'effective
code' , making {Joe AB 0} different than {Joe AB -1} as far as merging
intervals, and grouping for concatenation, is concerned. I admit that
"Joe;AB;0" is a strange name, but as long as you don't tell to Joe...


I assume you can solve the problem from this point

If not, you know where to ask



Vanderghast, Access MVP

I rechecked my previous posts descrpiton and everything is as it should be,
outside of the source data. In light of this I can understand your
confusion
as I made a most careless mistake in presenting my source data, it should
actually read as follows....I apologize for the mistake.

Name Arrival Departure Code Confo
Joe 11/1/2007 11/7/2007 AB 5345
Joe 11/9/2007 11/16/2007 AB 999
Joe 11/16/2007 11/23/2007 AB 176
Joe 11/23/2007 11/30/2007 AB 43
Joe 11/23/2007 11/30/2007 AB 789
Joe 12/14/2007 12/21/2007 GV 456

Consider the actual data:

Name Arrival Departure Code Confo
<a > Joe 11/9/2007 11/30/2007 AB 999
<b > Joe 11/16/2007 11/23/2007 AB 176
<c > Joe 11/23/2007 11/23/2007 AB 43
<d > Joe 11/23/2007 11/23/2007 AB 789

note that <a> has the same Name and same code than the other three
records,
and that its arrival-departure include intervals in <b>, <c> and <d>. But
in the result, you want <a> to be concatenated with ONLY <b> and,
furthermore, you want a second group made of <c> and <d> concatenated
together, ***BUT*** the second concatenation group, made of <c> and <d>,
get its departure date changed for the one of <a> (which is NOT part of
that second group, again, made only of <c> and <d> ).

At least, I fail to see how the 'data' at hand (without any other outside
reference) can determine the desired output.

<a, b> Joe 11/9/2007 11/23/2007 AB 176, 999
<c, d> Joe 11/23/2007 11/30/2007 AB 43, 789

(and the logic has to deal with the departure of < a, b > becoming to the
MINIMUM of departures dates from <a> and <b>, as well as <c, d> getting
the
MAXIMUM from <a, b> ??? )

Again, maybe there is some logic explaining it, with only the data at
hand,
but I don't see it for the moment.

Vanderghast, Access MVP

Hello Michel,

[quoted text clipped - 65 lines]

Thank you for your time and consideration.

 

[NeonSky via AccessMonster.com]

Hello Michel, thank you for your response. This certainly does make sense now,
though I am having a bit of trouble with the syntax of the below select
statement. Would you care to explain what the "1 <> (SELECT COUNT (*)" is
doingg? I understand rstand what it outputs, though from a definition
standpoint it would be great to better understandwhat it actually means (I
hope you get the gist of my vague distinction). Thanks!

That sounds easier. A possible strategy is to add a computed field, like:

SELECT Name, arrival, departure, code, confo,
1 <> ( SELECT COUNT(*)
FROM originalTable As b
WHERE a.name =b.name
AND a.arrival=b.arrival
AND a.departure=b.departure
AND a.code=b.code ) AS IsConcurrent

FROM originalTable AS b

which should return:

Name Arrival Departure Code Confo IsConcurrent
Joe 11/1/2007 11/7/2007 AB 5345 0
Joe 11/9/2007 11/16/2007 AB 999 0
Joe 11/16/2007 11/23/2007 AB 176 0
Joe 11/23/2007 11/30/2007 AB 43 -1
Joe 11/23/2007 11/30/2007 AB 789 -1
Joe 12/14/2007 12/21/2007 GV 456 0

and now, the tri-ple { name, code, isConcurrent } is the new 'name' to
consider, or, if you prefer, { code, isConcurrent } is the new 'effective
code' , making {Joe AB 0} different than {Joe AB -1} as far as merging
intervals, and grouping for concatenation, is concerned. I admit that
"Joe;AB;0" is a strange name, but as long as you don't tell to Joe...

I assume you can solve the problem from this point

If not, you know where to ask

Vanderghast, Access MVP

I rechecked my previous posts descrpiton and everything is as it should be,
outside of the source data. In light of this I can understand your

[quoted text clipped - 49 lines]

 

[Michel Walsh]

SELECT COUNT(*)
FROM originalTable AS b
WHERE a.name =b.name
AND a.arrival=b.arrival
AND a.departure=b.departure
AND a.code=b.code


count the number of records having the same name, arrival, departure and
code. If the count is different than one, this record is concurrent to (at
least) another one. How it does it? consider 'a' and 'b' the alias, as
'fingers', as two fingers over one single list. So, for the finger 'a' on a
row, the finger 'b' can also run, freely, over the list... well, not so
freely, since it must satisfy the WHERE clause to be part of the COUNT.



Hoping it may help,
Vanderghast, Access MVP

Hello Michel, thank you for your response. This certainly does make sense
now,
though I am having a bit of trouble with the syntax of the below select
statement. Would you care to explain what the "1 <> (SELECT COUNT (*)" is
doingg? I understand rstand what it outputs, though from a definition
standpoint it would be great to better understandwhat it actually means (I
hope you get the gist of my vague distinction). Thanks!

That sounds easier. A possible strategy is to add a computed field, like:

SELECT Name, arrival, departure, code, confo,
1 <> ( SELECT COUNT(*)
FROM originalTable As b
WHERE a.name =b.name
AND a.arrival=b.arrival
AND a.departure=b.departure
AND a.code=b.code ) AS IsConcurrent

FROM originalTable AS b

which should return:

Name Arrival Departure Code Confo IsConcurrent
Joe 11/1/2007 11/7/2007 AB 5345 0
Joe 11/9/2007 11/16/2007 AB 999 0
Joe 11/16/2007 11/23/2007 AB 176 0
Joe 11/23/2007 11/30/2007 AB 43 -1
Joe 11/23/2007 11/30/2007 AB 789 -1
Joe 12/14/2007 12/21/2007 GV 456 0

and now, the tri-ple { name, code, isConcurrent } is the new 'name' to
consider, or, if you prefer, { code, isConcurrent } is the new
'effective
code' , making {Joe AB 0} different than {Joe AB -1} as far as merging
intervals, and grouping for concatenation, is concerned. I admit that
"Joe;AB;0" is a strange name, but as long as you don't tell to Joe...

I assume you can solve the problem from this point

If not, you know where to ask

Vanderghast, Access MVP

I rechecked my previous posts descrpiton and everything is as it should
be,
outside of the source data. In light of this I can understand your

[quoted text clipped - 49 lines]

Thank you for your time and consideration.

 

[NeonSky via AccessMonster.com]

Hello Michel, so putting it together I have the following, though again am
running into syntax issues. Thanks for your patience as I am relatively new
using manual scripting (and man have I learned allot from your expertise)
Thanks.

SELECT COUNT (*)
FROM tblBase, tblBase As TempC
WHERE tblBase.Name = TempC.Name
AND tblBase.Arrival = TempC.Arrival
AND tblBase.Departure = TempC.Departure
AND tblBase.CODE = TempC.CODE
AS IsConcurrent
FROM tblBase AS TempC;

It would be great to have this churn out your example as seen below where the
IsConcurrent
field previously did not exist...

Name Arrival Departure Code Confo IsConcurrent
Joe 11/1/2007 11/7/2007 AB 5345 0
Joe 11/9/2007 11/16/2007 AB 999 0
Joe 11/16/2007 11/23/2007 AB 176 0
Joe 11/23/2007 11/30/2007 AB 43 -1
Joe 11/23/2007 11/30/2007 AB 789 -1
Joe 12/14/2007 12/21/2007 GV 456 0

As always thank you!

SELECT COUNT(*)
FROM originalTable AS b
WHERE a.name =b.name
AND a.arrival=b.arrival
AND a.departure=b.departure
AND a.code=b.code

count the number of records having the same name, arrival, departure and
code. If the count is different than one, this record is concurrent to (at
least) another one. How it does it? consider 'a' and 'b' the alias, as
'fingers', as two fingers over one single list. So, for the finger 'a' on a
row, the finger 'b' can also run, freely, over the list... well, not so
freely, since it must satisfy the WHERE clause to be part of the COUNT.

Hoping it may help,
Vanderghast, Access MVP

Hello Michel, thank you for your response. This certainly does make sense
now,

[quoted text clipped - 44 lines]

 

[Michel Walsh]

You should use
---------------------------------
SELECT Name, arrival, departure, code, confo,
1 <> ( SELECT COUNT(*)
FROM originalTable AS b
WHERE a.name =b.name
AND a.arrival=b.arrival
AND a.departure=b.departure
AND a.code=b.code ) AS IsConcurrent

FROM originalTable AS a
------------------------------------------------

when a SELECT statement is embedded into another one, it is always
surrounded by ( ).


My original statement, two messages ago, was in error in the last FROM:
FROM originalTable AS b. It should be AS a, not AS b, in the outer most
query, such as I pasted it in this message.



Vanderghast, Access MVP

Hello Michel, so putting it together I have the following, though again am
running into syntax issues. Thanks for your patience as I am relatively
new
using manual scripting (and man have I learned allot from your expertise)
Thanks.

SELECT COUNT (*)
FROM tblBase, tblBase As TempC
WHERE tblBase.Name = TempC.Name
AND tblBase.Arrival = TempC.Arrival
AND tblBase.Departure = TempC.Departure
AND tblBase.CODE = TempC.CODE
AS IsConcurrent
FROM tblBase AS TempC;

It would be great to have this churn out your example as seen below where
the
IsConcurrent
field previously did not exist...

Name Arrival Departure Code Confo IsConcurrent
Joe 11/1/2007 11/7/2007 AB 5345 0
Joe 11/9/2007 11/16/2007 AB 999 0
Joe 11/16/2007 11/23/2007 AB 176 0
Joe 11/23/2007 11/30/2007 AB 43 -1
Joe 11/23/2007 11/30/2007 AB 789 -1
Joe 12/14/2007 12/21/2007 GV 456 0

As always thank you!

SELECT COUNT(*)
FROM originalTable AS b
WHERE a.name =b.name
AND a.arrival=b.arrival
AND a.departure=b.departure
AND a.code=b.code

count the number of records having the same name, arrival, departure and
code. If the count is different than one, this record is concurrent to (at
least) another one. How it does it? consider 'a' and 'b' the alias, as
'fingers', as two fingers over one single list. So, for the finger 'a' on
a
row, the finger 'b' can also run, freely, over the list... well, not so
freely, since it must satisfy the WHERE clause to be part of the COUNT.

Hoping it may help,
Vanderghast, Access MVP

Hello Michel, thank you for your response. This certainly does make
sense
now,

[quoted text clipped - 44 lines]

Thank you for your time and consideration.

 

[NeonSky via AccessMonster.com]

Ok, I see. Though I receive a syntax error message that reads "Syntax error
(missing operator) in query expression and it highlights the "1" beneath the
initial select statement. My statement is as follows...Thanks!

SELECT Name, Arrival, Departure, CODE
1 <> ( SELECT COUNT(*)
FROM tblRawData AS TempC
WHERE TempC.Name = TempD.Name
AND TempC.Arrival = TempD.Arrival
AND TempC.Departure = TempD.Departure
AND TempC.CODE = TempD.CODE) As ISConcurrent
FROM tblRawData AS TempD;

You should use
---------------------------------
SELECT Name, arrival, departure, code, confo,
1 <> ( SELECT COUNT(*)
FROM originalTable AS b
WHERE a.name =b.name
AND a.arrival=b.arrival
AND a.departure=b.departure
AND a.code=b.code ) AS IsConcurrent

FROM originalTable AS a
------------------------------------------------

when a SELECT statement is embedded into another one, it is always
surrounded by ( ).

My original statement, two messages ago, was in error in the last FROM:
FROM originalTable AS b. It should be AS a, not AS b, in the outer most
query, such as I pasted it in this message.

Vanderghast, Access MVP

Hello Michel, so putting it together I have the following, though again am
running into syntax issues. Thanks for your patience as I am relatively

[quoted text clipped - 50 lines]

 

[Michel Walsh]

There is a missing coma, as delimiter for items in a list, after your field
name code and before the computed expression 1 <> ( SELECT ... ).


( I would be on the road for the next two days, I won't be able to answer
before next Monday).


Vanderghast, Access MVP

Ok, I see. Though I receive a syntax error message that reads "Syntax
error
(missing operator) in query expression and it highlights the "1" beneath
the
initial select statement. My statement is as follows...Thanks!

SELECT Name, Arrival, Departure, CODE
1 <> ( SELECT COUNT(*)
FROM tblRawData AS TempC
WHERE TempC.Name = TempD.Name
AND TempC.Arrival = TempD.Arrival
AND TempC.Departure = TempD.Departure
AND TempC.CODE = TempD.CODE) As ISConcurrent
FROM tblRawData AS TempD;

You should use
---------------------------------
SELECT Name, arrival, departure, code, confo,
1 <> ( SELECT COUNT(*)
FROM originalTable AS b
WHERE a.name =b.name
AND a.arrival=b.arrival
AND a.departure=b.departure
AND a.code=b.code ) AS IsConcurrent

FROM originalTable AS a
------------------------------------------------

when a SELECT statement is embedded into another one, it is always
surrounded by ( ).

My original statement, two messages ago, was in error in the last FROM:
FROM originalTable AS b. It should be AS a, not AS b, in the outer most
query, such as I pasted it in this message.

Vanderghast, Access MVP

Hello Michel, so putting it together I have the following, though again
am
running into syntax issues. Thanks for your patience as I am relatively

[quoted text clipped - 50 lines]

Thank you for your time and consideration.