Dont know where to start

[stephenrussett]

Ok i what i am trying to do is:

I have a query that pumps out information like this:
(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob
Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:
1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

[stephenrussett]

On Jun 14, 10:06 am, [e-mail] wrote:

Ok i what i am trying to do is:

I have a query that pumps out information like this:

(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

Is it possible to have access create what i want in a report?

[Neil]

Well, I almost got this to work entirely with derived tables. But,
apparently, Access chokes when you have a derived table within another
derived table. So you'll need one stored query to use as a subquery.

Assuming your table is called "MyTable," and the fields 1-5 below are called
Field1, Field2, etc., first, create a query called "Query1", and give it the
following SQL:

SELECT MyTable.Field4, Min(MyTable.Field2) AS Min2, Max(MyTable.Field2) AS
Max2
FROM MyTable
GROUP BY MyTable.Field4

(You can change the field and talbe names to fit your structure; but don't
change the aliases -- "AS Max2," etc.)

Once that's saved, create a second query, and give it the following SQL:

SELECT [a].[Min1] & " - " & [b].[Max1] AS Expr1, c.Field3, c.Field4,
c.Field5
FROM (SELECT MyTable.Field4, MyTable.Field1 AS Max1
FROM Query1
INNER JOIN MyTable ON (Query1.Max2 = MyTable.Field2) AND (Query1.Field4 =
MyTable.Field4)
) AS b INNER JOIN ((SELECT MyTable.Field4, MyTable.Field1 AS Min1
FROM Query1 INNER JOIN MyTable ON (Query1.Field4 = MyTable.Field4) AND
(Query1.Min2 = MyTable.Field2)
) AS a INNER JOIN (SELECT MyTable.Field3, MyTable.Field4, MyTable.Field5
FROM MyTable
GROUP BY MyTable.Field3, MyTable.Field4, MyTable.Field5
) AS c ON a.Field4 = c.Field4) ON b.Field4 = c.Field4

Again, you can rename the table and fields accordingly. That query should
give you the results below.

Once this second query is saved, you can either open it directly, or base a
report on the second query and display the results in a report.

Neil
<st************@gmail.comwrote in message
news:11**********************@o11g2000prd.googlegr oups.com...

On Jun 14, 10:06 am, [e-mail] wrote:

>Ok i what i am trying to do is:

I have a query that pumps out information like this:

(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

Is it possible to have access create what i want in a report?

[DavidB]

On Jun 14, 10:06 am, [e-mail] wrote:

Ok i what i am trying to do is:

I have a query that pumps out information like this:

(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;
Add fields as necessary

[Neil]

Your query doesn't produce the results he was looking for.

"DavidB" <je***@yahoo.comwrote in message
news:11**********************@i38g2000prf.googlegr oups.com...

On Jun 14, 10:06 am, [e-mail] wrote:

>Ok i what i am trying to do is:

I have a query that pumps out information like this:

(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;
Add fields as necessary

[DavidB]

On Jun 14, 1:24 pm, "Neil" <[e-mail]:

Your query doesn't produce the results he was looking for.

"DavidB" <[e-mail] in message

news:11**********************@i38g2000prf.googlegr oups.com...

On Jun 14, 10:06 am, [e-mail] wrote:

Ok i what i am trying to do is:

I have a query that pumps out information like this:

(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

Add fields as necessary- Hide quoted text -

- Show quoted text -

Sure it does

[Neil]

OK, putting his data, below, in a table, and calling the fields Field1,
Field2, etc., and running your SQL:

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

produces the following results:

Range Field2 Field3
1 - 6 1 Cooper Street
2 - 7 2 Cooper Street
10 - 3 3 Cooper Street
10A - 4 4 Cooper Street
5 - 5 5 Cooper Street

The results he was looking for were:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Even considering that you said he could add additional fields (for columns 4
and 5) the two results sets are clearly not the same. At the very least, one
has five records, the other has two. But the data is also not correct. You
still say your query produces correct results?

Neil
"DavidB" <je***@yahoo.comwrote in message
news:11**********************@a26g2000pre.googlegr oups.com...

On Jun 14, 1:24 pm, "Neil" <[e-mail]:

>Your query doesn't produce the results he was looking for.

"DavidB" <[e-mail] in message

news:11**********************@i38g2000prf.googleg roups.com...

On Jun 14, 10:06 am, [e-mail] wrote:
Ok i what i am trying to do is:

>I have a query that pumps out information like this:

>(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

>Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

>1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

>Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

Add fields as necessary- Hide quoted text -

- Show quoted text -

Sure it does

[stephenrussett]

On Jun 14, 11:27 pm, "Neil" <[e-mail]:

OK, putting his data, below, in a table, and calling the fields Field1,
Field2, etc., and running your SQL:

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

produces the following results:

Range Field2 Field3
1 - 6 1 Cooper Street
2 - 7 2 Cooper Street
10 - 3 3 Cooper Street
10A - 4 4 Cooper Street
5 - 5 5 Cooper Street

The results he was looking for were:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Even considering that you said he could add additional fields (for columns 4
and 5) the two results sets are clearly not the same. At the very least, one
has five records, the other has two. But the data is also not correct. You
still say your query produces correct results?

Neil

"DavidB" <[e-mail] in message

news:11**********************@a26g2000pre.googlegr oups.com...

On Jun 14, 1:24 pm, "Neil" <[e-mail]:

Your query doesn't produce the results he was looking for.

"DavidB" <[e-mail] in message

>news:11**********************@i38g2000prf.googleg roups.com...

On Jun 14, 10:06 am, [e-mail] wrote:
Ok i what i am trying to do is:

I have a query that pumps out information like this:

(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

Add fields as necessary- Hide quoted text -

- Show quoted text -

Sure it does- Hide quoted text -

- Show quoted text -

Neil can you explain your SQL to me. Like explain what your code
does. I am having troubles putting it to use and i wanted to make
sure i was not missing anything.

Thanks

[stephenrussett]

On Jun 15, 7:46 am, [e-mail] wrote:

On Jun 14, 11:27 pm, "Neil" <[e-mail]:

OK, putting his data, below, in a table, and calling the fields Field1,
Field2, etc., and running your SQL:

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

produces the following results:

Range Field2 Field3
1 - 6 1 Cooper Street
2 - 7 2 Cooper Street
10 - 3 3 Cooper Street
10A - 4 4 Cooper Street
5 - 5 5 Cooper Street

The results he was looking for were:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Even considering that you said he could add additional fields (for columns 4
and 5) the two results sets are clearly not the same. At the very least, one
has five records, the other has two. But the data is also not correct. You
still say your query produces correct results?

Neil

"DavidB" <[e-mail] in message

news:11**********************@a26g2000pre.googlegr oups.com...

On Jun 14, 1:24 pm, "Neil" <[e-mail]:
>Your query doesn't produce the results he was looking for.

>"DavidB" <[e-mail] in message

>>news:11**********************@i38g2000prf.google groups.com...

On Jun 14, 10:06 am, [e-mail] wrote:
>Ok i what i am trying to do is:

>I have a query that pumps out information like this:

>(1) (2) (3) (4) (5)
>1 1 Cooper Street NS Johnson & Dale
>2 2 Cooper Street NS Johnson & Dale
>3 3 Cooper Street NS Johnson & Dale
>4 4 Cooper Street NS Johnson & Dale
>5 5 Cooper Street NS Johnson & Dale
>6 1 Cooper Street SS Bail & Bob
>7 2 Cooper Street SS Bail & Bob
>10 3 Cooper Street SS Bail & Bob
>10A 4 Cooper Street SS Bail & Bob

>Ok so i have information that looks something like this. What i am
>trying to do is have excel open the database and show the information
>like this:

>1 - 5 Cooper Street NS Johnson & Dale
>6 - 10A Cooper Street SS Bail & Bob

>Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

Add fields as necessary- Hide quoted text -

>- Show quoted text -

Sure it does- Hide quoted text -

- Show quoted text -

Neil can you explain your SQL to me. Like explain what your code
does. I am having troubles putting it to use and i wanted to make
sure i was not missing anything.

Thanks- Hide quoted text -

- Show quoted text -

ok so i used this data as a sample

Field1 Field2 Field3 Field4 Field5
1 1 Cooper NS Jell & Bell
2 2 Cooper NS Jell & Bell
3A 3 Cooper NS Jell & Bell
4 4 Cooper NS Jell & Bell
5 1 Cooper SS Bob & Free
6 2 Cooper SS Bob & Free
7 3 Cooper SS Bob & Free
10 4 Cooper SS Bob & Free
12 5 Cooper SS Bob & Free
13A 1 Frank SS Bob & Free
13B 2 Frank SS Bob & Free
14C 3 Frank SS Bob & Free
And this is what i got from query 2

Expr1 Field3 Field4 Field5
1 - 4 Cooper NS Jell & Bell
13A - 12 Frank SS Bob & Free
13A - 12 Cooper SS Bob & Free
5 - 12 Frank SS Bob & Free
5 - 12 Cooper SS Bob & Free

Help?

[stephenrussett]

On Jun 15, 11:04 am, [e-mail] wrote:

On Jun 15, 7:46 am, [e-mail] wrote:

On Jun 14, 11:27 pm, "Neil" <[e-mail]:

OK, putting his data, below, in a table, and calling the fields Field1,
Field2, etc., and running your SQL:

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

produces the following results:

Range Field2 Field3
1 - 6 1 Cooper Street
2 - 7 2 Cooper Street
10 - 3 3 Cooper Street
10A - 4 4 Cooper Street
5 - 5 5 Cooper Street

The results he was looking for were:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Even considering that you said he could add additional fields (for columns 4
and 5) the two results sets are clearly not the same. At the very least, one
has five records, the other has two. But the data is also not correct. You
still say your query produces correct results?

Neil

"DavidB" <[e-mail] in message

>news:11**********************@a26g2000pre.googleg roups.com...

On Jun 14, 1:24 pm, "Neil" <[e-mail]:
Your query doesn't produce the results he was looking for.

"DavidB" <[e-mail] in message

>news:11**********************@i38g2000prf.googleg roups.com...

On Jun 14, 10:06 am, [e-mail] wrote:
Ok i what i am trying to do is:

I have a query that pumps out information like this:

(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

Add fields as necessary- Hide quoted text -

- Show quoted text -

Sure it does- Hide quoted text -

- Show quoted text -

Neil can you explain your SQL to me. Like explain what your code
does. I am having troubles putting it to use and i wanted to make
sure i was not missing anything.

Thanks- Hide quoted text -

- Show quoted text -

ok so i used this data as a sample

Field1 Field2 Field3 Field4 Field5
1 1 Cooper NS Jell & Bell
2 2 Cooper NS Jell & Bell
3A 3 Cooper NS Jell & Bell
4 4 Cooper NS Jell & Bell
5 1 Cooper SS Bob & Free
6 2 Cooper SS Bob & Free
7 3 Cooper SS Bob & Free
10 4 Cooper SS Bob & Free
12 5 Cooper SS Bob & Free
13A 1 Frank SS Bob & Free
13B 2 Frank SS Bob & Free
14C 3 Frank SS Bob & Free

And this is what i got from query 2

Expr1 Field3 Field4 Field5
1 - 4 Cooper NS Jell & Bell
13A - 12 Frank SS Bob & Free
13A - 12 Cooper SS Bob & Free
5 - 12 Frank SS Bob & Free
5 - 12 Cooper SS Bob & Free

Help?- Hide quoted text -

- Show quoted text -

I also need to be able to know how many Posts (Field 1) in Each block,

So for example, On Cooper NS Jell & Bell there are a total of 4 Posts
But how would i count this out when i Have the A,B,C etc etc next to
the number.

I also need to be able to sort Field 1 numerically. I have userd
CVar() and Val() in other querys to make it sort but i cant seem to
figure out how to do it with your SQL

[Neil]

On Jun 14, 10:06 am, [e-mail] wrote:
Ok i what i am trying to do is:

>I have a query that pumps out information like this:

>(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

>Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the
information
like this:

>1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

>Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range,
Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

Add fields as necessary- Hide quoted text -

Neil can you explain your SQL to me. Like explain what your code
does. I am having troubles putting it to use and i wanted to make
sure i was not missing anything.

Thanks

OK, the first query (Query1), which is:

SELECT MyTable.Field4, Min(MyTable.Field2) AS Min2, Max(MyTable.Field2) AS
Max2
FROM MyTable
GROUP BY MyTable.Field4

basically just gets the minimum and maximum values for field 2 for each
value in Field4. Here I made two assumptions:

- Field4 (the one containing NS and SS) is either the primary key field, or
at least works as an identifying value for the records

- The min/max values should be based on Field2, rather than Field1.The
assumption was that, as your values increased with each entry per Field4
value, that the min/max value of Field2 would also

The next query (the one which uses Query1 as a subquery), which is:

SELECT [a].[Min1] & " - " & [b].[Max1] AS Expr1, c.Field3, c.Field4,
c.Field5
FROM (SELECT MyTable.Field4, MyTable.Field1 AS Max1
FROM Query1
INNER JOIN MyTable ON (Query1.Max2 = MyTable.Field2) AND (Query1.Field4 =
MyTable.Field4)
) AS b
INNER JOIN ((SELECT MyTable.Field4, MyTable.Field1 AS Min1
FROM Query1 INNER JOIN MyTable ON (Query1.Field4 = MyTable.Field4) AND
(Query1.Min2 = MyTable.Field2)
) AS a
INNER JOIN (SELECT MyTable.Field3, MyTable.Field4, MyTable.Field5
FROM MyTable
GROUP BY MyTable.Field3, MyTable.Field4, MyTable.Field5
) AS c
ON a.Field4 = c.Field4) ON b.Field4 = c.Field4

uses three derived tables, and gives them the aliases "a", "b", and "c".

Derived table "b" (the first one) joins Query1 to the original table on the
Max and Field4 values, producing the Field1 value corresponding to the
Field2 max value.

Table "a" (the second one) does the same for the min values -- getting the
appropriate Field1 values.

Table "c" brings in fields 3 through 5, allowing them to be displayed along
with the Min1 and Max1 values at the beginning of the SQL statement.

[Neil]

>
ok so i used this data as a sample

Field1 Field2 Field3 Field4 Field5
1 1 Cooper NS Jell & Bell
2 2 Cooper NS Jell & Bell
3A 3 Cooper NS Jell & Bell
4 4 Cooper NS Jell & Bell
5 1 Cooper SS Bob & Free
6 2 Cooper SS Bob & Free
7 3 Cooper SS Bob & Free
10 4 Cooper SS Bob & Free
12 5 Cooper SS Bob & Free
13A 1 Frank SS Bob & Free
13B 2 Frank SS Bob & Free
14C 3 Frank SS Bob & Free
And this is what i got from query 2

Expr1 Field3 Field4 Field5
1 - 4 Cooper NS Jell & Bell
13A - 12 Frank SS Bob & Free
13A - 12 Cooper SS Bob & Free
5 - 12 Frank SS Bob & Free
5 - 12 Cooper SS Bob & Free

Help?

The problem here is that in your original data set, it appeared that Field4
was the identifier for the record. For each value in Field4 (NS or SS) the
values in Field3 and Field5 were uniform. Thus, I based the queries on using
Field4 as a primary key.

However, in your new set of data, there are two sets of Field3 values for
one of the Field4 values (SS). So the original assumptions do not apply.

The other assumption (noted in the other e-mail) was that Field2 would be
used to determine the Min/Max values, rather than Field1.

So, before we go any further, you need to give us more information about how
your tables are set up. What is your primary key field? Which field do you
want the min/max values derived from? How do want the data broken down?

Neil

[Neil]

>ok so i used this data as a sample

Field1 Field2 Field3 Field4 Field5
1 1 Cooper NS Jell & Bell
2 2 Cooper NS Jell & Bell
3A 3 Cooper NS Jell & Bell
4 4 Cooper NS Jell & Bell
5 1 Cooper SS Bob & Free
6 2 Cooper SS Bob & Free
7 3 Cooper SS Bob & Free
10 4 Cooper SS Bob & Free
12 5 Cooper SS Bob & Free
13A 1 Frank SS Bob & Free
13B 2 Frank SS Bob & Free
14C 3 Frank SS Bob & Free

And this is what i got from query 2

Expr1 Field3 Field4 Field5
1 - 4 Cooper NS Jell & Bell
13A - 12 Frank SS Bob & Free
13A - 12 Cooper SS Bob & Free
5 - 12 Frank SS Bob & Free
5 - 12 Cooper SS Bob & Free

Help?- Hide quoted text -

- Show quoted text -

I also need to be able to know how many Posts (Field 1) in Each block,

So for example, On Cooper NS Jell & Bell there are a total of 4 Posts
But how would i count this out when i Have the A,B,C etc etc next to
the number.

Val([Field1]) will give you the numeric value without the letter.

>
I also need to be able to sort Field 1 numerically. I have userd
CVar() and Val() in other querys to make it sort but i cant seem to
figure out how to do it with your SQL

You originally said you wanted the results to look like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

In what way would you want to sort Field1 numerically and apply it to these
results?

Neil

[DavidB]

On Jun 14, 11:27 pm, "Neil" <[e-mail]:

OK, putting his data, below, in a table, and calling the fields Field1,
Field2, etc., and running your SQL:

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

produces the following results:

Range Field2 Field3
1 - 6 1 Cooper Street
2 - 7 2 Cooper Street
10 - 3 3 Cooper Street
10A - 4 4 Cooper Street
5 - 5 5 Cooper Street

The results he was looking for were:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Even considering that you said he could add additional fields (for columns 4
and 5) the two results sets are clearly not the same. At the very least, one
has five records, the other has two. But the data is also not correct. You
still say your query produces correct results?

Neil

"DavidB" <[e-mail] in message

news:11**********************@a26g2000pre.googlegr oups.com...

On Jun 14, 1:24 pm, "Neil" <[e-mail]:

Your query doesn't produce the results he was looking for.

"DavidB" <[e-mail] in message

>news:11**********************@i38g2000prf.googleg roups.com...

On Jun 14, 10:06 am, [e-mail] wrote:
Ok i what i am trying to do is:

I have a query that pumps out information like this:

(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

Add fields as necessary- Hide quoted text -

- Show quoted text -

Sure it does- Hide quoted text -

- Show quoted text -

Well dumb ass you are suppoed to use the fields you want for field1
field2 field3 in the query nit just carte blance cut and paste! If
you cant figure that much out you have no business posting help in here

[DavidB]

On Jun 14, 10:06 am, [e-mail] wrote:

Ok i what i am trying to do is:

I have a query that pumps out information like this:

(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

Steven

the query i gave you waorks. you just need to drag the fields you
want in your result set into the query. that code was an example.
the solution really is very simple.

[Neil]

"DavidB" <je***@yahoo.comwrote in message
news:11**********************@u2g2000hsc.googlegro ups.com...

On Jun 14, 11:27 pm, "Neil" <[e-mail]:

>OK, putting his data, below, in a table, and calling the fields Field1,
Field2, etc., and running your SQL:

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range,
Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

produces the following results:

Range Field2 Field3
1 - 6 1 Cooper Street
2 - 7 2 Cooper Street
10 - 3 3 Cooper Street
10A - 4 4 Cooper Street
5 - 5 5 Cooper Street

The results he was looking for were:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Even considering that you said he could add additional fields (for
columns 4
and 5) the two results sets are clearly not the same. At the very least,
one
has five records, the other has two. But the data is also not correct.
You
still say your query produces correct results?

Neil

"DavidB" <[e-mail] in message

news:11**********************@a26g2000pre.googleg roups.com...

On Jun 14, 1:24 pm, "Neil" <[e-mail]:
Your query doesn't produce the results he was looking for.

>"DavidB" <[e-mail] in message

>>news:11**********************@i38g2000prf.google groups.com...

On Jun 14, 10:06 am, [e-mail] wrote:
Ok i what i am trying to do is:

>I have a query that pumps out information like this:

>(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

>Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the
information
like this:

>1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

>Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range,
Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

Add fields as necessary- Hide quoted text -

>- Show quoted text -

Sure it does- Hide quoted text -

- Show quoted text -

Well dumb ass you are suppoed to use the fields you want for field1
field2 field3 in the query nit just carte blance cut and paste! If
you cant figure that much out you have no business posting help in here

So why don't you give us an example of the query you'd use to obtain the
results he requested (1-5 Cooper Street, 6-10A Cooper Street). You post a
query that doesn't work, but you insist it does work. But you refuse to show
the query that you claim does work. That's because it doesn't. Period.

The data's right here:

>(Field1) (Field2) (Field3)
(Field4) (Field5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Your original post to Stephen was:

"SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

Add fields as necessary"

You are now saying that he shouldn't use Field1, Field2, and Field3 in the
SQL, but should use other fields instead. So why don't you just post the SQL
you'd use to get these results:

>1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

instead of just continually saying, "It works, it works," while it doesn't
work. Just post the SQL that actually works and you'll prove me wrong. But
since you can't do that (since it doesn't work, even if you change the
fields around), calling me names and just saying "It works" means absolutely
nothing. Desperate men resort to name-calling and abuse when they have no
rational argument.

Neil

[Chuck]

On Fri, 15 Jun 2007 10:46:46 -0700, DavidB <je***@yahoo.comwrote:

>On Jun 14, 10:06 am, [e-mail] wrote:

>Ok i what i am trying to do is:

Huge snip

>Steven

the query i gave you waorks. you just need to drag the fields you
want in your result set into the query. that code was an example.
the solution really is very simple.

DavidB,

Is there a news group that covers programming Logic?

Many of the questions in this news group look like homework assignments
designed to make the poster think. They are questions of logic. The solutions
have very little to do with Access other than this is where the logic will be
applied. And then to aggravate the situation, posters are looking for
specific solutions to vague questions.

Chuck
--

[rkc]

Chuck wrote:

Is there a news group that covers programming Logic?

Many of the questions in this news group look like homework assignments
designed to make the poster think. They are questions of logic. The solutions
have very little to do with Access other than this is where the logic will be
applied. And then to aggravate the situation, posters are looking for
specific solutions to vague questions.

I pasted this code into the command39_Click event. Why doesn't it work?

[Chuck]

On Sat, 16 Jun 2007 12:42:36 -0400, rkc <rk*@rkcny.yabba.dabba.do.comwrote:

>Chuck wrote:

>Is there a news group that covers programming Logic?

Many of the questions in this news group look like homework assignments
designed to make the poster think. They are questions of logic. The solutions
have very little to do with Access other than this is where the logic will be
applied. And then to aggravate the situation, posters are looking for
specific solutions to vague questions.

I pasted this code into the command39_Click event. Why doesn't it work?

Beautiful !!

Chuck
--

[stephenrussett]

On Jun 17, 8:19 am, Chuck <[e-mail]:

On Sat, 16 Jun 2007 12:42:36 -0400, rkc <[e-mail]:

Chuck wrote:

Is there a news group that covers programming Logic?

Many of the questions in this news group look like homework assignments
designed to make the poster think. They are questions of logic. The solutions
have very little to do with Access other than this is where the logic will be
applied. And then to aggravate the situation, posters are looking for
specific solutions to vague questions.

I pasted this code into the command39_Click event. Why doesn't it work?

Beautiful !!

Chuck
--

This is not a homework assignment.

As for the data My table uses a Unique ID but it does not need to be
inlcuded for this purpose.

I will get back to you with another table sample that will shows all
possible variations of data

[stephenrussett]

On Jun 18, 8:31 am, [e-mail] wrote:

On Jun 17, 8:19 am, Chuck <[e-mail]:

On Sat, 16 Jun 2007 12:42:36 -0400, rkc <[e-mail]:

>Chuck wrote:

>Is there a news group that covers programming Logic?

>Many of the questions in this news group look like homework assignments
>designed to make the poster think. They are questions of logic. The solutions
>have very little to do with Access other than this is where the logic will be
>applied. And then to aggravate the situation, posters are looking for
>specific solutions to vague questions.

>I pasted this code into the command39_Click event. Why doesn't it work?

Beautiful !!

Chuck
--

This is not a homework assignment.

As for the data My table uses a Unique ID but it does not need to be
inlcuded for this purpose.

I will get back to you with another table sample that will shows all
possible variations of data- Hide quoted text -

- Show quoted text -

Post Position Street Side Cross_Street Size Time_Limit Yoke
1 1 Sparks E.S. Lyon & Bay DV 1 Hour 2 Piece (New)
2 2 Sparks E.S. Lyon & Bay DV 1 Hour 2 Piece (New)
3 3 Sparks E.S. Lyon & Bay DV 1 Hour 2 Piece (New)
51 1 Sparks E.S. Bay & Lyon DV 1 Hour 2 Piece (New)
52 2 Sparks E.S. Bay & Lyon DV 1 Hour 2 Piece (New)
53 3 Sparks E.S. Bay & Lyon DV 1 Hour 2 Piece (New)
54 4 Sparks E.S. Bay & Lyon DV 1 Hour 2 Piece (New)
55 5 Sparks E.S. Bay & Lyon DV 1 Hour 2 Piece (New)
101 1 Alberto S.S. Met & O'Connor DV 1 Hour 2 Piece (New)
102 2 Alberto S.S. Met & O'Connor DV 1 Hour 2 Piece (New)
103 3 Alberto S.S. Met & O'Connor DV 1 Hour 1 Piece (Old)
104 4 Alberto S.S. Met & O'Connor DV 1 Hour 1 Piece (Old)
105 1 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
106 2 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
107 3 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
108 4 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
108A 5 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
108B 6 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
108C 7 Alberto N.S. O'Connor & Bank DV 1 Hour None
112 1 Alberto N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
122 1 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
123 2 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
124 3 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
125 4 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
126 5 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
127 6 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
128 1 Alberto N.S. Lyon & Bay DV 1 Hour None
129 2 Alberto N.S. Lyon & Bay DV 1 Hour None
130 3 Alberto N.S. Lyon & Bay DV 1 Hour None
131 4 Alberto N.S. Lyon & Bay DV 1 Hour 1 Piece (Old)
132 5 Alberto N.S. Lyon & Bay DV 1 Hour 1 Piece (Old)
133 1 Alberto N.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
134 2 Alberto N.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
135 3 Alberto N.S. Bay & Bronson DV 1 Hour None
136 1 Alberto S.S. Bay & Bronson DV 1 Hour None
137 2 Alberto S.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
138 3 Alberto S.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
139 4 Alberto S.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
140 5 Alberto S.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
201 1 Slater S.S. Bronson & Bay SV 2 Hour 2 Piece (New)
202 2 Slater S.S. Bronson & Bay SV 2 Hour 2 Piece (New)
203 3 Slater S.S. Bronson & Bay SV 2 Hour 2 Piece (New)
218 1 Slater S.S. Bay & Lyon SV 1 Piece (Old)
219 2 Slater S.S. Bay & Lyon SV 1 Piece (Old)
220 3 Slater S.S. Bay & Lyon SV 1 Piece (Old)
221 4 Slater S.S. Bay & Lyon SV 1 Piece (Old)
222 5 Slater S.S. Bay & Lyon SV 1 Piece (Old)
223 6 Slater S.S. Bay & Lyon SV 1 Piece (Old)
224 7 Slater S.S. Bay & Lyon SV 2 Piece (New)
225 8 Slater S.S. Bay & Lyon SV 2 Piece (New)
226 9 Slater S.S. Bay & Lyon SV None
227 1 Slater S.S. Lyon & Kent DV None
230 2 Slater S.S. Lyon & Kent DV 1 Piece (Old)
231 3 Slater S.S. Lyon & Kent DV 1 Piece (Old)
232 4 Slater S.S. Lyon & Kent DV None
233 1 Slater S.S. Kent & Bank DV 1 Piece (Old)
234 2 Slater S.S. Kent & Bank DV 1 Piece (Old)
235 3 Slater S.S. Kent & Bank DV 1 Piece (Old)
243 1 Slater S.S. O'Connor & Met DV None
244 2 Slater S.S. O'Connor & Met DV None
251 1 Slater N.S. Kent & Bank DV 1 Hour None
252 2 Slater N.S. Kent & Bank DV 1 Hour 1 Piece (Old)
253 3 Slater N.S. Kent & Bank DV 1 Hour 1 Piece (Old)
255 5 Slater N.S. Kent & Bank DV 1 Hour 1 Piece (Old)
256 6 Slater N.S. Kent & Bank DV 1 Hour 1 Piece (Old)
257 7 Slater N.S. Kent & Bank DV 1 Hour 1 Piece (Old)
301 1 John N.S. Lord & Met DV None
302 2 John N.S. Lord & Met DV None
303 3 John N.S. Lord & Met DV None
304 4 John N.S. Lord & Met DV None
318 1 John N.S. Met & O'Connor DV 1 Hour None
319 2 John N.S. Met & O'Connor DV 1 Hour None
320 3 John N.S. Met & O'Connor DV 1 Hour None
324 7 John N.S. Met & O'Connor DV 1 Hour None
325 8 John N.S. Met & O'Connor DV 1 Hour None
326 9 John N.S. Met & O'Connor DV 1 Hour None
334 1 John N.S. O'Connor & Bank DV 1 Hour None
335 2 John N.S. O'Connor & Bank DV 1 Hour None
343 1 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
344 2 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
345 3 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
346 4 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
347 5 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
348 6 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
349 7 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
365 1 John N.S. Kent & Lyon SV 2 Hour None
366 2 John N.S. Kent & Lyon SV 2 Hour None
367 3 John N.S. Kent & Lyon SV 2 Hour None
368 4 John N.S. Kent & Lyon SV 2 Hour None
369 5 John N.S. Kent & Lyon SV 2 Hour None
370 6 John N.S. Kent & Lyon SV 2 Hour None
401 1 Nepean W.S. Lyon & Kent SV 2 Hour None

[stephenrussett]

On Jun 18, 9:30 am, [e-mail] wrote:

On Jun 18, 8:31 am, [e-mail] wrote:

On Jun 17, 8:19 am, Chuck <[e-mail]:

On Sat, 16 Jun 2007 12:42:36 -0400, rkc <[e-mail]:
Chuck wrote:

Is there a news group that covers programming Logic?

Many of the questions in this news group look like homework assignments
designed to make the poster think. They are questions of logic. The solutions
have very little to do with Access other than this is where the logic will be
applied. And then to aggravate the situation, posters are looking for
specific solutions to vague questions.

I pasted this code into the command39_Click event. Why doesn't it work?

Beautiful !!

Chuck
--

This is not a homework assignment.

As for the data My table uses a Unique ID but it does not need to be
inlcuded for this purpose.

I will get back to you with another table sample that will shows all
possible variations of data- Hide quoted text -

- Show quoted text -

Post Position Street Side Cross_Street Size Time_Limit Yoke
1 1 Sparks E.S. Lyon & Bay DV 1 Hour 2 Piece (New)
2 2 Sparks E.S. Lyon & Bay DV 1 Hour 2 Piece (New)
3 3 Sparks E.S. Lyon & Bay DV 1 Hour 2 Piece (New)
51 1 Sparks E.S. Bay & Lyon DV 1 Hour 2 Piece (New)
52 2 Sparks E.S. Bay & Lyon DV 1 Hour 2 Piece (New)
53 3 Sparks E.S. Bay & Lyon DV 1 Hour 2 Piece (New)
54 4 Sparks E.S. Bay & Lyon DV 1 Hour 2 Piece (New)
55 5 Sparks E.S. Bay & Lyon DV 1 Hour 2 Piece (New)
101 1 Alberto S.S. Met & O'Connor DV 1 Hour 2 Piece (New)
102 2 Alberto S.S. Met & O'Connor DV 1 Hour 2 Piece (New)
103 3 Alberto S.S. Met & O'Connor DV 1 Hour 1 Piece (Old)
104 4 Alberto S.S. Met & O'Connor DV 1 Hour 1 Piece (Old)
105 1 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
106 2 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
107 3 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
108 4 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
108A 5 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
108B 6 Alberto N.S. O'Connor & Bank DV 1 Hour 1 Piece (Old)
108C 7 Alberto N.S. O'Connor & Bank DV 1 Hour None
112 1 Alberto N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
122 1 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
123 2 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
124 3 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
125 4 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
126 5 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
127 6 Alberto N.S. Kent & Lyon DV 1 Hour 1 Piece (Old)
128 1 Alberto N.S. Lyon & Bay DV 1 Hour None
129 2 Alberto N.S. Lyon & Bay DV 1 Hour None
130 3 Alberto N.S. Lyon & Bay DV 1 Hour None
131 4 Alberto N.S. Lyon & Bay DV 1 Hour 1 Piece (Old)
132 5 Alberto N.S. Lyon & Bay DV 1 Hour 1 Piece (Old)
133 1 Alberto N.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
134 2 Alberto N.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
135 3 Alberto N.S. Bay & Bronson DV 1 Hour None
136 1 Alberto S.S. Bay & Bronson DV 1 Hour None
137 2 Alberto S.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
138 3 Alberto S.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
139 4 Alberto S.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
140 5 Alberto S.S. Bay & Bronson DV 1 Hour 1 Piece (Old)
201 1 Slater S.S. Bronson & Bay SV 2 Hour 2 Piece (New)
202 2 Slater S.S. Bronson & Bay SV 2 Hour 2 Piece (New)
203 3 Slater S.S. Bronson & Bay SV 2 Hour 2 Piece (New)
218 1 Slater S.S. Bay & Lyon SV 1 Piece (Old)
219 2 Slater S.S. Bay & Lyon SV 1 Piece (Old)
220 3 Slater S.S. Bay & Lyon SV 1 Piece (Old)
221 4 Slater S.S. Bay & Lyon SV 1 Piece (Old)
222 5 Slater S.S. Bay & Lyon SV 1 Piece (Old)
223 6 Slater S.S. Bay & Lyon SV 1 Piece (Old)
224 7 Slater S.S. Bay & Lyon SV 2 Piece (New)
225 8 Slater S.S. Bay & Lyon SV 2 Piece (New)
226 9 Slater S.S. Bay & Lyon SV None
227 1 Slater S.S. Lyon & Kent DV None
230 2 Slater S.S. Lyon & Kent DV 1 Piece (Old)
231 3 Slater S.S. Lyon & Kent DV 1 Piece (Old)
232 4 Slater S.S. Lyon & Kent DV None
233 1 Slater S.S. Kent & Bank DV 1 Piece (Old)
234 2 Slater S.S. Kent & Bank DV 1 Piece (Old)
235 3 Slater S.S. Kent & Bank DV 1 Piece (Old)
243 1 Slater S.S. O'Connor & Met DV None
244 2 Slater S.S. O'Connor & Met DV None
251 1 Slater N.S. Kent & Bank DV 1 Hour None
252 2 Slater N.S. Kent & Bank DV 1 Hour 1 Piece (Old)
253 3 Slater N.S. Kent & Bank DV 1 Hour 1 Piece (Old)
255 5 Slater N.S. Kent & Bank DV 1 Hour 1 Piece (Old)
256 6 Slater N.S. Kent & Bank DV 1 Hour 1 Piece (Old)
257 7 Slater N.S. Kent & Bank DV 1 Hour 1 Piece (Old)
301 1 John N.S. Lord & Met DV None
302 2 John N.S. Lord & Met DV None
303 3 John N.S. Lord & Met DV None
304 4 John N.S. Lord & Met DV None
318 1 John N.S. Met & O'Connor DV 1 Hour None
319 2 John N.S. Met & O'Connor DV 1 Hour None
320 3 John N.S. Met & O'Connor DV 1 Hour None
324 7 John N.S. Met & O'Connor DV 1 Hour None
325 8 John N.S. Met & O'Connor DV 1 Hour None
326 9 John N.S. Met & O'Connor DV 1 Hour None
334 1 John N.S. O'Connor & Bank DV 1 Hour None
335 2 John N.S. O'Connor & Bank DV 1 Hour None
343 1 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
344 2 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
345 3 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
346 4 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
347 5 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
348 6 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
349 7 John N.S. Bank & Kent DV 1 Hour 1 Piece (Old)
365 1 John N.S. Kent & Lyon SV 2 Hour None
366 2 John N.S. Kent & Lyon SV 2 Hour None
367 3 John N.S. Kent & Lyon SV 2 Hour None
368 4 John N.S. Kent & Lyon SV 2 Hour None
369 5 John N.S. Kent & Lyon SV 2 Hour None
370 6 John N.S. Kent & Lyon SV 2 Hour None
401 1 Nepean W.S. Lyon & Kent SV 2 Hour None- Hide quoted text -

- Show quoted text -

Ok the data is a little messed but it should still convey what i am
talking about. This made up data that represents how it is set up.