Re: cant think of the query...
From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 07/21/04
- Next message: Tom Ellison: "Re: Unique Count - Syntax Problem"
- Previous message: Steve: "Ignore spaces in criteria"
- In reply to: Dragon: "Re: cant think of the query..."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 21 Jul 2004 12:25:03 -0500
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"
<NoSpam_Baadil@hotmail.com> 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" <tellison@jcdoyle.com> wrote in message
>news:95krf0p9c622a9133k0ptav8om0ii4ki00@4ax.com...
>> 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"
>> <NoSpam_Baadil@hotmail.com> 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" <tellison@jcdoyle.com> wrote in message
>> >news:mdarf010oths5ddbkoaqjuvd1rlanr3mog@4ax.com...
>> >> 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 are:
>> >>
>> >> - 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"
>> >> <NoSpam_Baadil@hotmail.com> 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.
>> >> >
>> >>
>> >
>>
>
- Next message: Tom Ellison: "Re: Unique Count - Syntax Problem"
- Previous message: Steve: "Ignore spaces in criteria"
- In reply to: Dragon: "Re: cant think of the query..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
