I need to find top 3 records from my table

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

I have a large table with mean scrap reasons that I want to be able to run a
query to find only to top three for a given date range.

Table design:
Date PartNumber reason1 reason2 reason3 ect
5/1/09 XXXXXX1 20 0 0
5/2/09 XXXXXX 0 5 0
5/3/09 XXXXXX1 0 7 0
5/5/09 XXXXXX 4 0 0
5/5/09 XXXXXX1 0 7 0
5/4/09 XXXXXX1 0 9 0
5/5/09 XXXXXX1 0 0 5
5/6/09 XXXXXX1 0 0 10

I have 3 different part Numbers and 15 reasons not all the reasons will have
a value but I want to run a query that shows only the top 3 individual
reasons for my date range. Is there a way to run a query that will do that?

desired result:
Date PartNumber reason1 reason2 reason3 ect
5/1/09 XXXXXX1 20 0 0
5/6/09 XXXXXX1 0 0 10
5/4/09 XXXXXX1 0 9 0

I have looked through the database here and it seems that several people
have done this but I can't seem to get my Select statement to work.
 
First, some clarifying questions:

1) Define "top 3". Is that in reference to the value in the "reasonx"
fields? Is that in reference to some other filtering value?

2) You want to return the "top 3" for each Date field's value in the table?

3) What is "ect" and how is it supposed to relate to your desired "top 3"
query result?

Also, note that you should not name a field "Date". It and many other words
are reserved words in ACCESS and should not be used for control names, field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html


See these Knowledge Base articles for more information about reserved words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
SELECT TOP 3 *
FROM table
ORDER BY reason1 + reason2 + reason3 + etc DESC


If you need to limit the dates, add a WHERE clause after the FROM and before
the ORDER BY clauses.
That assumes you have 0, not null, under any reasonN fields.


Your data seems to not be normalized.




Vanderghast, Access MVP
 
Sorry about the "ect" first off it was only to show that there where more
reason columns. You are correct in the reasonN they are "0" not null.
What is the meaning of not normalized? I can have values in reason1 and
reason2 for a given date and part number. I have also removed all the spaces
from my field names.
 
Normalized or not is not a disease (at least, as I see it) and not an
absolute matter, but more about the way to arrange the data in the table so
that the tool at hand (the database engine) will simplify OUR job. Having
fields like Reason1, Reason2, Reason3 is generally a bad idea because:

- if you need Reason4, you need to add the field, to the table, AND to
change the FORMS using that table, so they now display that new control too,
AND probably the SQL statements implying that table (Maintenance problems)
- nothing really forbid someone, half asleep, to mistakingly repeat
Reason1 in Reason7 field (user input error detections)
- deleting Reason2 could be easily done, by putting a 0 in it, but
then, when a new reason is to be added, you have the extra work to look at
the first 'reason' available (spotting data)
- space is used to accomodate the maximum number of reasons, even if
most records may have only one reason!

So, instead of:

ClientID PartNumber Jan Feb Mar Apr
1010 1011 1 0 0 5
1010 1034 0 0 0 2


An alternative design can be:

ClientID PartNumber MonthName Amount
1010 1011 Jan 1
1010 1011 Apr 5
1010 1034 Apr 2


Sure, sure, sure, it is harder to read, for a HUMAN person, but much better
for the database tool (and anyhow, you can switch it back in an easier
reading presentation with a Crosstab query, if required)

As example,you can define an index made of the three first fields, not
allowing duplication, and then, no one would be able to enter TWO records
for the same client, same PartNumber, same MonthName. It is easy to add a
new amount for a new MonthName, given a client and a part number: append a
record. It is easy to delete an amount for a month, given a client and a
part number: you delete the record. Search of the maximum amount per
client is easy: GROUP on client and MAX(amount)... the first design would
oblige us to scan all the month fields, horizontally... but SQL does not
work horizontally, without supplying ALL the involved fields.


Note that in your case, if "Reason1" is just a place holder, ie, not like a
month name, not and indication that it is more important than "Reason2", the
alternate design can be simply:


ClientID PartNumber Amount
1010 1011 1
1010 1011 5
1010 1034 2



Anyhow, the alternate design will be easier to answer to the question: what
is the TOTAL amount, per client, per part|Number:


SELECT clientID, partNumber, SUM(amont) As totalAmount
FROM alternateDesign
GROUP BY clientID, partNumber



while, with the initial design, it would have been:

SELECT clientID, partNumber, Reason1+Reason2+Reason3+ ... + Reason32
FROM firstDesign


and YOU will have to modify that query if ever a Reason33 field is added to
the table!


If you want to find the maximum amount, per client, per part number:

SELECT clientID, partNumber, MAX(amount)
FROM alternateDesign
GROUP BY clientID, partNumber


while, with the first design... well, I don't want to type iif until
tomorrow morning...




Sure, to get the TOP 3, alternate design:

SELECT TOP 3 clientID, partNumber, SUM(amont) As totalAmount
FROM alternateDesign
GROUP BY clientID, partNumber
ORDER BY SUM(amount) DESC


compared with the first design:

SELECT TOP 3 clientID, partNumber, Reason1+Reason2+Reason3+ ... + Reason32
FROM firstDesign
ORDER BY Reason1+Reason2+Reason3+ ... + Reason32 DESC


the immediate advantage (typing) is not evident, but still, maintenance
problem (adding Reason33) are possible. The alternate (normalized) design
can add as many reasons as you have space, since you add RECORDS, not
FIELDS. (Well, we are limited to a total of 2Gig, but that is a very large
limit, usually)




So, again, normalization is not much an absolute. As example: would we
use:

ClientID FirstName MiddleName LastName
1010 Joe W Blow

or


ClientID NamePosition Name
1010 1 Joe
1010 2 W
1010 3 Blow


or


ClientID ClientName
1010 Jow W Blow



Well, that depends! Generally the third design will be use, even if it less
normalized than the first, which is itself less normalized than the second
design. To the question: up to were we normalize? my answer is: up to what
you really need. If you never need to decompose the ClientName, clearly, the
third design is the way to go, here.


So, I don't really know why used "Reason1" , "Reason2" , and so on, but
such progression, xxx1, xxx2, xxx3, ... , on the fields names is OFTEN a
poor design, for a database.





Vanderghast, Access MVP
 
Sorry again for the confusion I did not label my fields as reason1 reason2
and so on, they all have individual names. tThis is for a production report
we are writing, so the field names are like Flash, Gas burns, splay. ect... I
have created a form that you fill in the desired amounts which in this case
are numbers and the other fields get a "0" in them. I would like to be able
to sort the table from the fields to find the top 3 problems for a given date
range. Some days we have only 1 issue and other days we have many reasons for
throwing parts away and I need to be able to run a report that shows the top
3 problems from my table.

I hope that clearifies things a little I was only trying to make it easier
to understand by using reason1 reason2. I have one table where all the
information is stored is that the best way???? it makes my table have a large
number of fields. but it allows me to control the type of information entered
since you can call a problem or misspell the word and that would cause a
whole other set of problems. I have created this in excel but wanted a way to
use accesses report writer to give me better control of the data.
 
With your actual design,


SELECT TOP 3 date, partNumber, reason1+reason2+reason3+...+reason32
FROM table
ORDER BY reason1+readon2+reason3+...+reason32 DESC


would give the TOP 3 date+partNumber where the sum of values in the 32
reasons (or whatever is their real name). If you need a criteria to limit
the dates, add a WHERE clause between the FROM and the ORDER BY clause.


With a normalized table,

date partNumber Reason amount
x 1010 flash 5
x 1010 splay 2
x+1 1010 gas burn 1
x+1 1010 flash 2


the solution is:


SELECT TOP 3 date, partNumber, SUM(amount)
FROM table
GROUP BY date, partNumber
ORDER BY SUM(amount)



Now, to consider only reason2 and reason3 (or only flash and gas burn):

SELECT TOP 3 date, partNumber, reason2+reason3
FROM table
ORDER BY reason2+reason3


for the first table design, and we have to re-write the SQL statment since
the implied field names have to be explicitly given. For the normalized
design, a first try can be:

SELECT TOP 3 date, partNumber, SUM(amount)
FROM table
WHERE reason IN("flash", "gas burn")
GROUP BY date, partNumber
ORDER BY SUM(amount)



That works, but the IN list is 'fix'. Better to have a table,
ImpliedReasons, one field, its primary key, reason, and fill the table with
reason we want to be implied (one value per record), an use the following
SQL statement:

SELECT TOP 3 date, partNumber, SUM(amount)
FROM table INNER JOIN impliedReasons
ON table.reason = impliedReasons.Reason
GROUP BY date, partNumber
ORDER BY SUM(amount)


And now, to run the query with other implied reasons, we only have to change
the DATA in the table impliedReason INSTEAD OF CHANGING THE SQL CODE. After
all, it is easy to change data in a table, with a FORM or otherwise, much
easier and safer, than to change the SQL statement, isn't it?


So, not only you don't have to code a large number of SQL statments (or to
allow the end user to play with working code), but it can also accomodate
new 'reasons' without requering your modifications of working code.


You mention the possibility of typo, say, someone typing flahs instead of
flash. That is EASY with relationnal database: define a table, refReasons,
one field reason, its primary key. Next define a relation between this
refReasons table AND your normalized table, over their common field, reason.
Enforce the relation: it is now impossible to enter a reason which is NOT IN
refReasons.reason. Even better, make the user use a FORM to enter data, and
supply a COMBO BOX rather than a free text edit control!

To normalize your initial data, you can first make a query:


SELECT date, partNumber, "flash" AS reason, flash FROM table
UNION ALL
SELECT date, partNumber, "gas burn" AS reason, gasBurn FROM table
UNION ALL
SELECT date, partNumber, "splash" AS reason, splash FROM table
UNION ALL
....


(it ends with a SELECT, not with UNION ALL )


and to make your table, make a table with data from this query (and next,
add index).





Vanderghast, Access MVP
 
I hope that clearifies things a little I was only trying to make it easier
to understand by using reason1 reason2. I have one table where all the
information is stored is that the best way????
Click to expand...

No, unforutnately it's a very bad way. The trouble you're having is just one
example of why.
it makes my table have a large
number of fields. but it allows me to control the type of information entered
since you can call a problem or misspell the word and that would cause a
whole other set of problems. I have created this in excel but wanted a way to
use accesses report writer to give me better control of the data.
Click to expand...

The proper way to handle this uses THREE tables:

Parts
PartNumber <Primary Key>
PartName
<other information about the part as a thing in itself>

Issues <or some other appropriate tablename>
IssueID <autonumber primary key>
Issue <e.g. "Flash", "Splay", whatever you now have in fieldnames>

IssuesSeen <or other appropriate tablename>
IssuesSeenID <autonumber primary key>
IssueID <Long Integer, link to Issues>
PartNumber <link to Parts>
SeenDate <date/time>
NumberSeen

If you have thirteen issues with a part, you would not use 13 *fields* but
rather thirteen *records*. It then becomes very easy to sum them up using a
Totals query, or to search for the dates of all of the "Flash" issues, etc.
etc.
 
Back
Top