cant think of the query...

Hi,

I am trying to compare two files with fields but can't think of a way to do
this.Here is what I have


Table: Versions

xField1 xField2 xField3 ... xProdVer
abs sdsa asdsad FinalProd12V1.0
asa sdsd fghgfhfg FinalProd12V2.0
asa sdsd fghgfhfg FinalProd12V2.1
asa sdsd fghgfhfg FinalProd55V1.0
asa sdsd fghgfhfg FinalProd55V2.0
asa sdsd fghgfhfg FinalProd55V3.0

Table: Site1
nField1 nField2 nProdVer
asa sdsd FinalProd12V2.0
asa sdsd FinalProd55V3.0

Versions table contains all new and old versions of a product. Site1 Table
contains products in use at that site. I need to get the following result:

xField1 nField1 xField2 nField2 xProdVer nProdVer
abs asa sdsa sdsd FinalProd12V2.1
FinalProd12V2.0
asa asa sdsd sdsd FinalProd55V3.0
FinalProd55V3.0

Essentially I want to create a list of all products in Site1 and show the
current version along with the latest version available for that product. I
want to all items in the Site1 table comparing with Products table and
showing either the matching or a later version ofthe product.

Sorry if I am not being clear enough.

Thanks.

Dear Dragon:

OK, so perhaps you are wanting to find the latest row in table
Versions where xField1 is 'asa' and xProdVer starts with
"FinalProd12V" that being the one where the rest of xProdVer is the
"largest" value you can find. Is that the thing here?

It is difficult, nay impossible, for me to guess what rules make one
value of xProdVer "newer" than another one. Do all the values in
xProdVer start with "FinalProd"? Is there always next a 2 digit value
which, along with xField1 specifies which product is involved? And
does it always end with a version major of one digit, then a period,
then a version minor of one digit?

The thing that is making this very difficult with which to work is
improper design of what I THINK your information means. If all the
xProdVer values start with "FinalProd" you could just as well leave
that off. But the big things a

- the value 12 or 55 shown which is part of what distinguishes one
product from another.

- the version major value

- the version minor value

The principle is called atomicity. Don't mix different pieces of
information in one column. Make these three into separate columns,
I'll call ProductMinor (12 or 55), VersionMajor, and VersionMinor.
From these pieces you can very easily assemble the thing you now call
xProdVer when you need it, but you can also much more easily perform
query work like what you need here.

So, I suggest the following in your tables:

Table: Versions

xField1 xField2 xField3 ... ProductMinor VersionMajor
Version Minor
abs sdsa asdsad 12 1
0
asa sdsd fghgfhfg 12 2
0
asa sdsd fghgfhfg 12 2
1
asa sdsd fghgfhfg 55 1
0
asa sdsd fghgfhfg 55 2
0
asa sdsd fghgfhfg 55 3
0

Table: Site1
nField1 nField2 ProductMinor VersionMajor VersionMinor
asa sdsd 12 2 0
asa sdsd 55 3 0

From the above, your problem admits to much more specific description.
For each row in Site1, consider the NField1 / ProductMinor values.
Find the rows in Versions that have the same values where NField1 =
xField1 and ProductMinors are the same. From among those, find all
rows which have the highest value of VersionMajor, and from among
those rows, the one with the largest VersionMinor. That does the job.

The query work to do this isn't simple novice stuff, but it is now
well defined and doable. And you will be able to build indexes that
can improve its performance. As it was, you'd be hard pressed to code
it, and then the performance would not be as easily tuned.

Make sense so far? If this is acceptable, we can proceed to actual
query work next.

I'm not sure what part, if any, xField2 and nField2 play. And xField3
is a complete mystery to me. Can we safely ignore them for what you
need here?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 20 Jul 2004 15:42:41 -0700, "Dragon"
wrote:

Hi,

I am trying to compare two files with fields but can't think of a way to do
this.Here is what I have


Table: Versions

xField1 xField2 xField3 ... xProdVer
abs sdsa asdsad FinalProd12V1.0
asa sdsd fghgfhfg FinalProd12V2.0
asa sdsd fghgfhfg FinalProd12V2.1
asa sdsd fghgfhfg FinalProd55V1.0
asa sdsd fghgfhfg FinalProd55V2.0
asa sdsd fghgfhfg FinalProd55V3.0

Table: Site1
nField1 nField2 nProdVer
asa sdsd FinalProd12V2.0
asa sdsd FinalProd55V3.0

Versions table contains all new and old versions of a product. Site1 Table
contains products in use at that site. I need to get the following result:

xField1 nField1 xField2 nField2 xProdVer nProdVer
abs asa sdsa sdsd FinalProd12V2.1
FinalProd12V2.0
asa asa sdsd sdsd FinalProd55V3.0
FinalProd55V3.0

Essentially I want to create a list of all products in Site1 and show the
current version along with the latest version available for that product. I
want to all items in the Site1 table comparing with Products table and
showing either the matching or a later version ofthe product.

Sorry if I am not being clear enough.

Thanks.

Thank you Tom for your detailed reply.

The tables I am working with are not permanent tables. Performance is not an
issue at all here. I get the Master Product list in an Excel worksheet while
I get the site data in a text file. Then I import those into my Access
database to find out if I need to update any product at any site.

Here is some actual data:
Master Table
ID Name Type Template Name FWVersion
1 B301010i PQ0301 PQ030188d PQ030189d PQ030190c PQ030191d PQ030192c
PQ030193d PQ030194c PQ030195d V1.00.06
2 B301010j PQ0301 PQ030188e PQ030189e PQ030190e PQ030191e PQ030192e
PQ030193e PQ030194e PQ030195e V1.00.06a


Site Table
ID Location Name Template NewType NewTemplate NewFWVersion
1 TP1 B301010I PQ30192a
2 TP1 B301010I PQ30192C
3 TP2 B301010J PQ30192C
4 TP2 B301010J PQ30192E


Result Should be:
ID Location Name Template NewType NewTemplate NewFWVersion
1 TP1 B301010I PQ30192a B301010J PQ030192e V1.00.06a
2 TP1 B301010I PQ30192C B301010J PQ030192e V1.00.06a
3 TP2 B301010J PQ30191C Current PQ030191e V1.00.06a
4 TP2 B301010J PQ30192E Current Current V1.00.06a


In 'Name' Field only Last Char changes for different version. All numbers
changes when there is a different product. For example next product might be
B301259Q.
In 'Template' Field, Only last char changes. Each product can have a number
of templates (in the same field).
There is no existing FWVersion data in Site table so it will always get the
data from the latest product release.

I hope this clarifies a bit more. Please let me know if I am asking for too
much. I understand that it might be a bad design but I am very to how I get
the data.

Thank you.

"Tom Ellison" wrote in message
...
Dear Dragon:

OK, so perhaps you are wanting to find the latest row in table
Versions where xField1 is 'asa' and xProdVer starts with
"FinalProd12V" that being the one where the rest of xProdVer is the
"largest" value you can find. Is that the thing here?

It is difficult, nay impossible, for me to guess what rules make one
value of xProdVer "newer" than another one. Do all the values in
xProdVer start with "FinalProd"? Is there always next a 2 digit value
which, along with xField1 specifies which product is involved? And
does it always end with a version major of one digit, then a period,
then a version minor of one digit?

The thing that is making this very difficult with which to work is
improper design of what I THINK your information means. If all the
xProdVer values start with "FinalProd" you could just as well leave
that off. But the big things a

- the value 12 or 55 shown which is part of what distinguishes one
product from another.

- the version major value

- the version minor value

The principle is called atomicity. Don't mix different pieces of
information in one column. Make these three into separate columns,
I'll call ProductMinor (12 or 55), VersionMajor, and VersionMinor.
From these pieces you can very easily assemble the thing you now call
xProdVer when you need it, but you can also much more easily perform
query work like what you need here.

So, I suggest the following in your tables:

Table: Versions

xField1 xField2 xField3 ... ProductMinor VersionMajor
Version Minor
abs sdsa asdsad 12 1
0
asa sdsd fghgfhfg 12 2
0
asa sdsd fghgfhfg 12 2
1
asa sdsd fghgfhfg 55 1
0
asa sdsd fghgfhfg 55 2
0
asa sdsd fghgfhfg 55 3
0

Table: Site1
nField1 nField2 ProductMinor VersionMajor VersionMinor
asa sdsd 12 2 0
asa sdsd 55 3 0

From the above, your problem admits to much more specific description.
For each row in Site1, consider the NField1 / ProductMinor values.
Find the rows in Versions that have the same values where NField1 =
xField1 and ProductMinors are the same. From among those, find all
rows which have the highest value of VersionMajor, and from among
those rows, the one with the largest VersionMinor. That does the job.

The query work to do this isn't simple novice stuff, but it is now
well defined and doable. And you will be able to build indexes that
can improve its performance. As it was, you'd be hard pressed to code
it, and then the performance would not be as easily tuned.

Make sense so far? If this is acceptable, we can proceed to actual
query work next.

I'm not sure what part, if any, xField2 and nField2 play. And xField3
is a complete mystery to me. Can we safely ignore them for what you
need here?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 20 Jul 2004 15:42:41 -0700, "Dragon"
wrote:

Hi,

I am trying to compare two files with fields but can't think of a way to
do
this.Here is what I have


Table: Versions

xField1 xField2 xField3 ... xProdVer
abs sdsa asdsad FinalProd12V1.0
asa sdsd fghgfhfg FinalProd12V2.0
asa sdsd fghgfhfg FinalProd12V2.1
asa sdsd fghgfhfg FinalProd55V1.0
asa sdsd fghgfhfg FinalProd55V2.0
asa sdsd fghgfhfg FinalProd55V3.0

Table: Site1
nField1 nField2 nProdVer
asa sdsd FinalProd12V2.0
asa sdsd FinalProd55V3.0

Versions table contains all new and old versions of a product. Site1
Table
contains products in use at that site. I need to get the following
result:

xField1 nField1 xField2 nField2 xProdVer
nProdVer
abs asa sdsa sdsd FinalProd12V2.1
FinalProd12V2.0
asa asa sdsd sdsd FinalProd55V3.0
FinalProd55V3.0

Essentially I want to create a list of all products in Site1 and show the
current version along with the latest version available for that product.
I
want to all items in the Site1 table comparing with Products table and
showing either the matching or a later version ofthe product.

Sorry if I am not being clear enough.

Thanks.

Dear Dragon:

In this sample data, it appears the tables are related on the column
containing B30101010i and nothing else. Is that correct.

If so, this is it the case that you want the latest FWVersion value
from among all those in the Master table with the matching [Name]
value. Finally, if this is correct, how do you define which is the
newest version number? Probably this is by a "Dewey Decimal" method,
which will not sort or compare in the computer properly, unless you
pad with leading zeros so all the components of the values being
compared have the same number of digits.

For example, if V9.37.18a is followed by V10.00.00, then you will have
to do considerable programming in order to product a comparison
function. That's because, in order to have a value with multiple
decimal places, as well as letters, the column must be a text type.
Text types compare alphabetically, not numerically, and will not
compare as expected unless all the decimal places are filled
identically. In the above example, V10.00 comes BEFORE V9.37 simply
because 1 comes before 9.

That's the major reason I recommended breaking up the version into its
component pieces, so they can actually be compared.

However, I don't know enough about your existing and FUTURE data to be
able to analyze this in depth. Possibly these versions are just being
typed in by users, in which case V01.7.3 probably means exactly the
same as V1.07.03. It may to a human reader, but without considerable
programming effort, it won't to a computer. That's the essence of the
"Dewey Decimal" problem.

If you break this into columns containing integers 1, 7, and 3, then
the computer can compare them without ambiguity. Since these form, in
essence, a hierarchy of sorting, the computer needs to compare them
separately in numerical form to give the normally expected effect.
So, again, they need to be stored in separate columns in the table so
you can compare and manipulate them readily.

If you persist in wanting to keep the Version in a single column, you
will be faced with programming a function that effectively splits them
into components and compares the components, returning perhaps -1 for
less than, 0 for same, and 1 for greater than. If you do that you
should be able to get the query to do what you want, but this will be
an unindexable result. While you say you aren't concerned with
performance, when you build something like this inefficiently and it
takes hours to process you might just change your mind about that.

If it is not convenient to separate the information in the table, you
might want to do so with a query. It now looks like you have 4 parts
to a Version number. From your example: V1.00.06a has components 1,
0, 6, and 'a'. In order to compare two of these in a function or as
columns in a query, you would need to compare them one constituent
porting at a time from left to right till a difference is detected.

Another issue that can be helped by changing the table design is that
you should be concerned to prevent duplicating version numbers for the
same product. In some cases, this could have a bad effect on query
results.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 20 Jul 2004 17:31:05 -0700, "Dragon"
wrote:

Thank you Tom for your detailed reply.

The tables I am working with are not permanent tables. Performance is not an
issue at all here. I get the Master Product list in an Excel worksheet while
I get the site data in a text file. Then I import those into my Access
database to find out if I need to update any product at any site.

Here is some actual data:
Master Table
ID Name Type Template Name FWVersion
1 B301010i PQ0301 PQ030188d PQ030189d PQ030190c PQ030191d PQ030192c
PQ030193d PQ030194c PQ030195d V1.00.06
2 B301010j PQ0301 PQ030188e PQ030189e PQ030190e PQ030191e PQ030192e
PQ030193e PQ030194e PQ030195e V1.00.06a


Site Table
ID Location Name Template NewType NewTemplate NewFWVersion
1 TP1 B301010I PQ30192a
2 TP1 B301010I PQ30192C
3 TP2 B301010J PQ30192C
4 TP2 B301010J PQ30192E


Result Should be:
ID Location Name Template NewType NewTemplate NewFWVersion
1 TP1 B301010I PQ30192a B301010J PQ030192e V1.00.06a
2 TP1 B301010I PQ30192C B301010J PQ030192e V1.00.06a
3 TP2 B301010J PQ30191C Current PQ030191e V1.00.06a
4 TP2 B301010J PQ30192E Current Current V1.00.06a


In 'Name' Field only Last Char changes for different version. All numbers
changes when there is a different product. For example next product might be
B301259Q.
In 'Template' Field, Only last char changes. Each product can have a number
of templates (in the same field).
There is no existing FWVersion data in Site table so it will always get the
data from the latest product release.

I hope this clarifies a bit more. Please let me know if I am asking for too
much. I understand that it might be a bad design but I am very to how I get
the data.

Thank you.

"Tom Ellison" wrote in message
.. .
Dear Dragon:

OK, so perhaps you are wanting to find the latest row in table
Versions where xField1 is 'asa' and xProdVer starts with
"FinalProd12V" that being the one where the rest of xProdVer is the
"largest" value you can find. Is that the thing here?

It is difficult, nay impossible, for me to guess what rules make one
value of xProdVer "newer" than another one. Do all the values in
xProdVer start with "FinalProd"? Is there always next a 2 digit value
which, along with xField1 specifies which product is involved? And
does it always end with a version major of one digit, then a period,
then a version minor of one digit?

The thing that is making this very difficult with which to work is
improper design of what I THINK your information means. If all the
xProdVer values start with "FinalProd" you could just as well leave
that off. But the big things a

- the value 12 or 55 shown which is part of what distinguishes one
product from another.

- the version major value

- the version minor value

The principle is called atomicity. Don't mix different pieces of
information in one column. Make these three into separate columns,
I'll call ProductMinor (12 or 55), VersionMajor, and VersionMinor.
From these pieces you can very easily assemble the thing you now call
xProdVer when you need it, but you can also much more easily perform
query work like what you need here.

So, I suggest the following in your tables:

Table: Versions

xField1 xField2 xField3 ... ProductMinor VersionMajor
Version Minor
abs sdsa asdsad 12 1
0
asa sdsd fghgfhfg 12 2
0
asa sdsd fghgfhfg 12 2
1
asa sdsd fghgfhfg 55 1
0
asa sdsd fghgfhfg 55 2
0
asa sdsd fghgfhfg 55 3
0

Table: Site1
nField1 nField2 ProductMinor VersionMajor VersionMinor
asa sdsd 12 2 0
asa sdsd 55 3 0

From the above, your problem admits to much more specific description.
For each row in Site1, consider the NField1 / ProductMinor values.
Find the rows in Versions that have the same values where NField1 =
xField1 and ProductMinors are the same. From among those, find all
rows which have the highest value of VersionMajor, and from among
those rows, the one with the largest VersionMinor. That does the job.

The query work to do this isn't simple novice stuff, but it is now
well defined and doable. And you will be able to build indexes that
can improve its performance. As it was, you'd be hard pressed to code
it, and then the performance would not be as easily tuned.

Make sense so far? If this is acceptable, we can proceed to actual
query work next.

I'm not sure what part, if any, xField2 and nField2 play. And xField3
is a complete mystery to me. Can we safely ignore them for what you
need here?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 20 Jul 2004 15:42:41 -0700, "Dragon"
wrote:

Hi,

I am trying to compare two files with fields but can't think of a way to
do
this.Here is what I have


Table: Versions

xField1 xField2 xField3 ... xProdVer
abs sdsa asdsad FinalProd12V1.0
asa sdsd fghgfhfg FinalProd12V2.0
asa sdsd fghgfhfg FinalProd12V2.1
asa sdsd fghgfhfg FinalProd55V1.0
asa sdsd fghgfhfg FinalProd55V2.0
asa sdsd fghgfhfg FinalProd55V3.0

Table: Site1
nField1 nField2 nProdVer
asa sdsd FinalProd12V2.0
asa sdsd FinalProd55V3.0

Versions table contains all new and old versions of a product. Site1
Table
contains products in use at that site. I need to get the following
result:

xField1 nField1 xField2 nField2 xProdVer
nProdVer
abs asa sdsa sdsd FinalProd12V2.1
FinalProd12V2.0
asa asa sdsd sdsd FinalProd55V3.0
FinalProd55V3.0

Essentially I want to create a list of all products in Site1 and show the
current version along with the latest version available for that product.
I
want to all items in the Site1 table comparing with Products table and
showing either the matching or a later version ofthe product.

Sorry if I am not being clear enough.

Thanks.

Hi Tom,

You are correct in saying that the only common column is B301010i etc. As
far as FWversion is concerned, every product has a version. I don't need to
compare this as I will simply copy this value into the result. My main
concern is to compare the product (B301010i) to the master table using only
B301010 (without the last chat) and then find a matching record with the
newest char, for example If there are records in the mater table for
B301010i, B301010j, B301010k, I want the B301010K to be returned.

As far as Template field (PQ30190i) is concerned , there is always a match
for the site template in the master list. Only the last character may
differ. Also, this template match will always be on the same record found
with matching B301010.


"Tom Ellison" wrote in message
...
Dear Dragon:

In this sample data, it appears the tables are related on the column
containing B30101010i and nothing else. Is that correct.

If so, this is it the case that you want the latest FWVersion value
from among all those in the Master table with the matching [Name]
value. Finally, if this is correct, how do you define which is the
newest version number? Probably this is by a "Dewey Decimal" method,
which will not sort or compare in the computer properly, unless you
pad with leading zeros so all the components of the values being
compared have the same number of digits.

For example, if V9.37.18a is followed by V10.00.00, then you will have
to do considerable programming in order to product a comparison
function. That's because, in order to have a value with multiple
decimal places, as well as letters, the column must be a text type.
Text types compare alphabetically, not numerically, and will not
compare as expected unless all the decimal places are filled
identically. In the above example, V10.00 comes BEFORE V9.37 simply
because 1 comes before 9.

That's the major reason I recommended breaking up the version into its
component pieces, so they can actually be compared.

However, I don't know enough about your existing and FUTURE data to be
able to analyze this in depth. Possibly these versions are just being
typed in by users, in which case V01.7.3 probably means exactly the
same as V1.07.03. It may to a human reader, but without considerable
programming effort, it won't to a computer. That's the essence of the
"Dewey Decimal" problem.

If you break this into columns containing integers 1, 7, and 3, then
the computer can compare them without ambiguity. Since these form, in
essence, a hierarchy of sorting, the computer needs to compare them
separately in numerical form to give the normally expected effect.
So, again, they need to be stored in separate columns in the table so
you can compare and manipulate them readily.

If you persist in wanting to keep the Version in a single column, you
will be faced with programming a function that effectively splits them
into components and compares the components, returning perhaps -1 for
less than, 0 for same, and 1 for greater than. If you do that you
should be able to get the query to do what you want, but this will be
an unindexable result. While you say you aren't concerned with
performance, when you build something like this inefficiently and it
takes hours to process you might just change your mind about that.

If it is not convenient to separate the information in the table, you
might want to do so with a query. It now looks like you have 4 parts
to a Version number. From your example: V1.00.06a has components 1,
0, 6, and 'a'. In order to compare two of these in a function or as
columns in a query, you would need to compare them one constituent
porting at a time from left to right till a difference is detected.

Another issue that can be helped by changing the table design is that
you should be concerned to prevent duplicating version numbers for the
same product. In some cases, this could have a bad effect on query
results.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 20 Jul 2004 17:31:05 -0700, "Dragon"
wrote:

Thank you Tom for your detailed reply.

The tables I am working with are not permanent tables. Performance is not
an
issue at all here. I get the Master Product list in an Excel worksheet
while
I get the site data in a text file. Then I import those into my Access
database to find out if I need to update any product at any site.

Here is some actual data:
Master Table
ID Name Type Template Name FWVersion
1 B301010i PQ0301 PQ030188d PQ030189d PQ030190c PQ030191d PQ030192c
PQ030193d PQ030194c PQ030195d V1.00.06
2 B301010j PQ0301 PQ030188e PQ030189e PQ030190e PQ030191e PQ030192e
PQ030193e PQ030194e PQ030195e V1.00.06a


Site Table
ID Location Name Template NewType NewTemplate NewFWVersion
1 TP1 B301010I PQ30192a
2 TP1 B301010I PQ30192C
3 TP2 B301010J PQ30192C
4 TP2 B301010J PQ30192E


Result Should be:
ID Location Name Template NewType NewTemplate NewFWVersion
1 TP1 B301010I PQ30192a B301010J PQ030192e V1.00.06a
2 TP1 B301010I PQ30192C B301010J PQ030192e V1.00.06a
3 TP2 B301010J PQ30191C Current PQ030191e V1.00.06a
4 TP2 B301010J PQ30192E Current Current V1.00.06a


In 'Name' Field only Last Char changes for different version. All numbers
changes when there is a different product. For example next product might
be
B301259Q.
In 'Template' Field, Only last char changes. Each product can have a
number
of templates (in the same field).
There is no existing FWVersion data in Site table so it will always get
the
data from the latest product release.

I hope this clarifies a bit more. Please let me know if I am asking for
too
much. I understand that it might be a bad design but I am very to how I
get
the data.

Thank you.

"Tom Ellison" wrote in message
.. .
Dear Dragon:

OK, so perhaps you are wanting to find the latest row in table
Versions where xField1 is 'asa' and xProdVer starts with
"FinalProd12V" that being the one where the rest of xProdVer is the
"largest" value you can find. Is that the thing here?

It is difficult, nay impossible, for me to guess what rules make one
value of xProdVer "newer" than another one. Do all the values in
xProdVer start with "FinalProd"? Is there always next a 2 digit value
which, along with xField1 specifies which product is involved? And
does it always end with a version major of one digit, then a period,
then a version minor of one digit?

The thing that is making this very difficult with which to work is
improper design of what I THINK your information means. If all the
xProdVer values start with "FinalProd" you could just as well leave
that off. But the big things a

- the value 12 or 55 shown which is part of what distinguishes one
product from another.

- the version major value

- the version minor value

The principle is called atomicity. Don't mix different pieces of
information in one column. Make these three into separate columns,
I'll call ProductMinor (12 or 55), VersionMajor, and VersionMinor.
From these pieces you can very easily assemble the thing you now call
xProdVer when you need it, but you can also much more easily perform
query work like what you need here.

So, I suggest the following in your tables:

Table: Versions

xField1 xField2 xField3 ... ProductMinor VersionMajor
Version Minor
abs sdsa asdsad 12 1
0
asa sdsd fghgfhfg 12 2
0
asa sdsd fghgfhfg 12 2
1
asa sdsd fghgfhfg 55 1
0
asa sdsd fghgfhfg 55 2
0
asa sdsd fghgfhfg 55 3
0

Table: Site1
nField1 nField2 ProductMinor VersionMajor VersionMinor
asa sdsd 12 2 0
asa sdsd 55 3 0

From the above, your problem admits to much more specific description.
For each row in Site1, consider the NField1 / ProductMinor values.
Find the rows in Versions that have the same values where NField1 =
xField1 and ProductMinors are the same. From among those, find all
rows which have the highest value of VersionMajor, and from among
those rows, the one with the largest VersionMinor. That does the job.

The query work to do this isn't simple novice stuff, but it is now
well defined and doable. And you will be able to build indexes that
can improve its performance. As it was, you'd be hard pressed to code
it, and then the performance would not be as easily tuned.

Make sense so far? If this is acceptable, we can proceed to actual
query work next.

I'm not sure what part, if any, xField2 and nField2 play. And xField3
is a complete mystery to me. Can we safely ignore them for what you
need here?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 20 Jul 2004 15:42:41 -0700, "Dragon"
wrote:

Hi,

I am trying to compare two files with fields but can't think of a way
to
do
this.Here is what I have


Table: Versions

xField1 xField2 xField3 ... xProdVer
abs sdsa asdsad FinalProd12V1.0
asa sdsd fghgfhfg FinalProd12V2.0
asa sdsd fghgfhfg FinalProd12V2.1
asa sdsd fghgfhfg FinalProd55V1.0
asa sdsd fghgfhfg FinalProd55V2.0
asa sdsd fghgfhfg FinalProd55V3.0

Table: Site1
nField1 nField2 nProdVer
asa sdsd FinalProd12V2.0
asa sdsd FinalProd55V3.0

Versions table contains all new and old versions of a product. Site1
Table
contains products in use at that site. I need to get the following
result:

xField1 nField1 xField2 nField2 xProdVer
nProdVer
abs asa sdsa sdsd FinalProd12V2.1
FinalProd12V2.0
asa asa sdsd sdsd FinalProd55V3.0
FinalProd55V3.0

Essentially I want to create a list of all products in Site1 and show
the
current version along with the latest version available for that
product.
I
want to all items in the Site1 table comparing with Products table and
showing either the matching or a later version ofthe product.

Sorry if I am not being clear enough.

Thanks.

Dear Dragon:

I feel like I have now been filled in on an important detail.

Using all but the last character of Site.Name, you JOIN this to all
but the last character of Master.Name, but then you limit the multiple
rows in Master found this way to the one row with the alphabetically
highest last character.

At this point, I would strongly recommend you change both tables to
have two columns to contain what you now have in [Name], splitting off
the last character. Your database will work better and be easier to
query this way. This is the principle of normalization called
atomicity.

Using what you have now, I would use a cross-product of the tables
since they cannot really be JOINed, and then filter:

WHERE LEFT(Site.[Name], 7) = LEFT(Master.[Name], 7)
AND RIGHT(Master.[Name], 1) =
(SELECT MAX(RIGHT(M1.[Name], 1)) FROM Master M1
WHERE LEFT(M1.[Name], 7) = LEFT(Site.[Name], 7))

This limits the rows from Master to those with the same first 7
characters, and within those rows to the one row with the
alphabetically last (maximum) last character.

This is the type of technique that should do the job you want.

If you make two columns out of [Name] as I suggested, two things
happen. You would no longer need all the LEFT() and RIGHT() functions
in the above code I gave you, making this simpler to write. But also,
the database engine could be given indexing of these two columns, and
would be able to find matches with great rapidity. So, it will
perform much better.

In conclusion, I will say that databases are build around the rules of
normalization, and that those rules are built around what will make
databases work more easily and with best performance. When we diverge
from those, the result can be predicted: More difficult coding and
poorer performance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 21 Jul 2004 09:47:37 -0700, "Dragon"
wrote:

Hi Tom,

You are correct in saying that the only common column is B301010i etc. As
far as FWversion is concerned, every product has a version. I don't need to
compare this as I will simply copy this value into the result. My main
concern is to compare the product (B301010i) to the master table using only
B301010 (without the last chat) and then find a matching record with the
newest char, for example If there are records in the mater table for
B301010i, B301010j, B301010k, I want the B301010K to be returned.

As far as Template field (PQ30190i) is concerned , there is always a match
for the site template in the master list. Only the last character may
differ. Also, this template match will always be on the same record found
with matching B301010.


"Tom Ellison" wrote in message
.. .
Dear Dragon:

In this sample data, it appears the tables are related on the column
containing B30101010i and nothing else. Is that correct.

If so, this is it the case that you want the latest FWVersion value
from among all those in the Master table with the matching [Name]
value. Finally, if this is correct, how do you define which is the
newest version number? Probably this is by a "Dewey Decimal" method,
which will not sort or compare in the computer properly, unless you
pad with leading zeros so all the components of the values being
compared have the same number of digits.

For example, if V9.37.18a is followed by V10.00.00, then you will have
to do considerable programming in order to product a comparison
function. That's because, in order to have a value with multiple
decimal places, as well as letters, the column must be a text type.
Text types compare alphabetically, not numerically, and will not
compare as expected unless all the decimal places are filled
identically. In the above example, V10.00 comes BEFORE V9.37 simply
because 1 comes before 9.

That's the major reason I recommended breaking up the version into its
component pieces, so they can actually be compared.

However, I don't know enough about your existing and FUTURE data to be
able to analyze this in depth. Possibly these versions are just being
typed in by users, in which case V01.7.3 probably means exactly the
same as V1.07.03. It may to a human reader, but without considerable
programming effort, it won't to a computer. That's the essence of the
"Dewey Decimal" problem.

If you break this into columns containing integers 1, 7, and 3, then
the computer can compare them without ambiguity. Since these form, in
essence, a hierarchy of sorting, the computer needs to compare them
separately in numerical form to give the normally expected effect.
So, again, they need to be stored in separate columns in the table so
you can compare and manipulate them readily.

If you persist in wanting to keep the Version in a single column, you
will be faced with programming a function that effectively splits them
into components and compares the components, returning perhaps -1 for
less than, 0 for same, and 1 for greater than. If you do that you
should be able to get the query to do what you want, but this will be
an unindexable result. While you say you aren't concerned with
performance, when you build something like this inefficiently and it
takes hours to process you might just change your mind about that.

If it is not convenient to separate the information in the table, you
might want to do so with a query. It now looks like you have 4 parts
to a Version number. From your example: V1.00.06a has components 1,
0, 6, and 'a'. In order to compare two of these in a function or as
columns in a query, you would need to compare them one constituent
porting at a time from left to right till a difference is detected.

Another issue that can be helped by changing the table design is that
you should be concerned to prevent duplicating version numbers for the
same product. In some cases, this could have a bad effect on query
results.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 20 Jul 2004 17:31:05 -0700, "Dragon"
wrote:

Thank you Tom for your detailed reply.

The tables I am working with are not permanent tables. Performance is not
an
issue at all here. I get the Master Product list in an Excel worksheet
while
I get the site data in a text file. Then I import those into my Access
database to find out if I need to update any product at any site.

Here is some actual data:
Master Table
ID Name Type Template Name FWVersion
1 B301010i PQ0301 PQ030188d PQ030189d PQ030190c PQ030191d PQ030192c
PQ030193d PQ030194c PQ030195d V1.00.06
2 B301010j PQ0301 PQ030188e PQ030189e PQ030190e PQ030191e PQ030192e
PQ030193e PQ030194e PQ030195e V1.00.06a


Site Table
ID Location Name Template NewType NewTemplate NewFWVersion
1 TP1 B301010I PQ30192a
2 TP1 B301010I PQ30192C
3 TP2 B301010J PQ30192C
4 TP2 B301010J PQ30192E


Result Should be:
ID Location Name Template NewType NewTemplate NewFWVersion
1 TP1 B301010I PQ30192a B301010J PQ030192e V1.00.06a
2 TP1 B301010I PQ30192C B301010J PQ030192e V1.00.06a
3 TP2 B301010J PQ30191C Current PQ030191e V1.00.06a
4 TP2 B301010J PQ30192E Current Current V1.00.06a


In 'Name' Field only Last Char changes for different version. All numbers
changes when there is a different product. For example next product might
be
B301259Q.
In 'Template' Field, Only last char changes. Each product can have a
number
of templates (in the same field).
There is no existing FWVersion data in Site table so it will always get
the
data from the latest product release.

I hope this clarifies a bit more. Please let me know if I am asking for
too
much. I understand that it might be a bad design but I am very to how I
get
the data.

Thank you.

"Tom Ellison" wrote in message
.. .
Dear Dragon:

OK, so perhaps you are wanting to find the latest row in table
Versions where xField1 is 'asa' and xProdVer starts with
"FinalProd12V" that being the one where the rest of xProdVer is the
"largest" value you can find. Is that the thing here?

It is difficult, nay impossible, for me to guess what rules make one
value of xProdVer "newer" than another one. Do all the values in
xProdVer start with "FinalProd"? Is there always next a 2 digit value
which, along with xField1 specifies which product is involved? And
does it always end with a version major of one digit, then a period,
then a version minor of one digit?

The thing that is making this very difficult with which to work is
improper design of what I THINK your information means. If all the
xProdVer values start with "FinalProd" you could just as well leave
that off. But the big things a

- the value 12 or 55 shown which is part of what distinguishes one
product from another.

- the version major value

- the version minor value

The principle is called atomicity. Don't mix different pieces of
information in one column. Make these three into separate columns,
I'll call ProductMinor (12 or 55), VersionMajor, and VersionMinor.
From these pieces you can very easily assemble the thing you now call
xProdVer when you need it, but you can also much more easily perform
query work like what you need here.

So, I suggest the following in your tables:

Table: Versions

xField1 xField2 xField3 ... ProductMinor VersionMajor
Version Minor
abs sdsa asdsad 12 1
0
asa sdsd fghgfhfg 12 2
0
asa sdsd fghgfhfg 12 2
1
asa sdsd fghgfhfg 55 1
0
asa sdsd fghgfhfg 55 2
0
asa sdsd fghgfhfg 55 3
0

Table: Site1
nField1 nField2 ProductMinor VersionMajor VersionMinor
asa sdsd 12 2 0
asa sdsd 55 3 0

From the above, your problem admits to much more specific description.
For each row in Site1, consider the NField1 / ProductMinor values.
Find the rows in Versions that have the same values where NField1 =
xField1 and ProductMinors are the same. From among those, find all
rows which have the highest value of VersionMajor, and from among
those rows, the one with the largest VersionMinor. That does the job.

The query work to do this isn't simple novice stuff, but it is now
well defined and doable. And you will be able to build indexes that
can improve its performance. As it was, you'd be hard pressed to code
it, and then the performance would not be as easily tuned.

Make sense so far? If this is acceptable, we can proceed to actual
query work next.

I'm not sure what part, if any, xField2 and nField2 play. And xField3
is a complete mystery to me. Can we safely ignore them for what you
need here?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 20 Jul 2004 15:42:41 -0700, "Dragon"
wrote:

Hi,

I am trying to compare two files with fields but can't think of a way
to
do
this.Here is what I have


Table: Versions

xField1 xField2 xField3 ... xProdVer
abs sdsa asdsad FinalProd12V1.0
asa sdsd fghgfhfg FinalProd12V2.0
asa sdsd fghgfhfg FinalProd12V2.1
asa sdsd fghgfhfg FinalProd55V1.0
asa sdsd fghgfhfg FinalProd55V2.0
asa sdsd fghgfhfg FinalProd55V3.0

Table: Site1
nField1 nField2 nProdVer
asa sdsd FinalProd12V2.0
asa sdsd FinalProd55V3.0

Versions table contains all new and old versions of a product. Site1
Table
contains products in use at that site. I need to get the following
result:

xField1 nField1 xField2 nField2 xProdVer
nProdVer
abs asa sdsa sdsd FinalProd12V2.1
FinalProd12V2.0
asa asa sdsd sdsd FinalProd55V3.0
FinalProd55V3.0

Essentially I want to create a list of all products in Site1 and show
the
current version along with the latest version available for that
product.
I
want to all items in the Site1 table comparing with Products table and
showing either the matching or a later version ofthe product.

Sorry if I am not being clear enough.

Thanks.