Solved

Need to condense numbers in Access Table based on the latest number

Posted on 2013-05-24
9
266 Views
Last Modified: 2013-06-12
Pictured below is my table. I had to use a multi step query to extend out all my sub numbers. Now I need to remove all the rows that lead up to the last row.

For Example 1637276M1 through 1637276M6 should be deleted and 1637276M7 should be kept with all the sub number to the right of it.

How could i delete those rows?

condense
0
Comment
Question by:bignadad
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I'm confused...

Can you also post a graphical example of the *exact* output you need here.
0
 
LVL 8

Expert Comment

by:agillanders
Comment Utility
Probably easiest would be to copy the correct data into a temporary table by sorting in reverse order and using SELECT (TOP 1) in a sub-query to isolate just the record you want for each core part number.

However I think there is a deeper issue here because the table design is badly non-normalized and there appears to be no need for it. Repeating fields (all the OEMSubNumber fields) are VERY bad practice and can make life very difficult  down the road. I think a better design might be a master table along the lines of:

PartNumber, SubCode, SubNumber, OEMPartNumber, OEMDescription, IsActive, Parent

eg: 1637276, M, 1, 1637276M1, USE PN 1637276M2, False, 0
to 1637276, M, 7, 1637276M7, USE PN 1637276M8, True, 1637276M6

Then you could generate your current view on the fly with a Crosstab query plus filtering can be done based on aggregate functions since you want to keep Max(OEMSubNumber)

Ths will give you a normalized design and should still give the output you need. I use a similar design in a generic version control system where each change is logged and I can then produce a detailed version history very easily. Essentially it's a 1-directional linked list. Or if you need to be able to browse both ways make it a bidirectional linked list. That depends on your specific application.

Alistair
0
 
LVL 2

Author Comment

by:bignadad
Comment Utility
@boag2000
This is the result im after
what to keep
@agillanders
I have heard many people mention how denormalized my table is ever since i started and no one has offered to explain how to go about normalizing it.
With your example i see what your talking about. however the [PartNumber, SubCode, SubNumber] would be touch to achieve. im working with part numbers from many different vendors and they all are different in schemes.

I do like the idea of the isactive and parent though. question is how the hell do i get it to that point from where im at now.

When i started with this table it had everything in 3 columns. OEMPartNumber, OEMDescription, OEMSubNumber.

Problem with that is i am using this as a cross reference to retrieve all possible oem numbers and match it to the OEMItem that is in my AMI table. Once i get that matched it can display my AMI Part Number.

So to get all these sub numbers in line with the main number i have been doing a multiple step query and it worked will for John Deere table, but with this table the sub numbers are in a cascading order and that is causing me issues.

This is the Step7 query im using to get my sub numbers to match to the main OEM Part number
SELECT DuplicateStep6.OEMPartNumber, DuplicateStep6.OEMDescription, DuplicateStep6.OEMSubNumber, DuplicateStep6.OEMSubNumber2, DuplicateStep6.OEMSubNumber3, DuplicateStep6.OEMSubNumber4, DuplicateStep6.OEMSubNumber5, DuplicateStep6.OEMSubNumber6, Agco.OEMPartNumber AS OEMSubNumber7
FROM DuplicateStep6 LEFT JOIN Agco ON DuplicateStep6.OEMSubNumber6 = Agco.OEMSubNumber

Open in new window


So if i went back to my original table and tried to get in the format you suggest how would i do that? could you explain if i started a new question?
this is the way the table looks in my original table before i started my step query

original table
0
 
LVL 2

Author Comment

by:bignadad
Comment Utility
@agillanders
It also might be worth noting that i don't care the part is obsolete/discontinued.

All i care about is finding every possible OEM Number that subs to one another and comparing those to my OEMItem in my AMI Table. That is what retrieves my AMI Item number
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 8

Accepted Solution

by:
agillanders earned 500 total points
Comment Utility
On normalization there are libraries filled with stuff on this (see Wiki as a start). However an excellent start is to achieve what is called 3rd Normal Form (3NF). The more advanced NF's become ever more finicky and are generally not a big issue unless you are designing a major database likely to have multiple different and varying clients.

Again, the language gets complicated. I like the tongue-in-cheek summary. Essentially you have:

1NF: No repeating fields!
2NF: 1NF + All data fields should depend upon the whole of the key fields.
3NF: 2NF + All data fields should depend on nothing other than the key fields.

2NF assumes multiple field keys (which with natural keys is inevitable) - it is trivially correct for single field keys.

This can be summarized in the mantra:
"No repeating fields and all fields shall depend upon the key, the whole key, and nothing but the key. So help me Codd."

E F Codd is credited as the inventor of the relational model and normalization.

As for implementation a little bit depends on what the fields mean. It looks very like the SubNumber is actually a pointer to the replacement? Or is that just an accident of the data you're using? Is it true that each PartNumber can only have 0 or 1 SubNumber's or is that again an accident of the example data?

Basically to normalize properly it is impolrtant to understand the real relationships within the data and that is not clear to me here. You have a list in the form string, string, string but the 3rd string appears to be a foreign key from other records in the table...and as such the constraints need to be explored to correctly define a path forward.

For example: in the abstract do you have a list, a linked-list, a tree or a graph? How do you tell?
1. List = no relationship between records
2. Linked list = each record points to its predecessor and/or successor.
3. Tree = each record can have 0-1 parent, but 0-n children.
4. Graph = each record can have 0-n connections to other records (optionally directional)

Trees also get called hierarchies, graphs get called networks (e.g. the Web).

Implementation gets more complex as you move up that list - but it is very important to recognize what you really have because fixing it later is a nightmare. To paraphrase Einstein - a model should be as simple as possible, and no simpler!:-)

Alistair
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
:) Hi.

How did this end-up in Access? (Other questions of a similar nature have this in SQL Server)
and these images display OEMSubNumber7 - oh dear....

May I make this observation? This table structure is causing you endless trouble and what really needs to happen is that it should be normalized (i.e. this is the table structure that is transposed to multiple rows to facilitate searching)
0
 
LVL 2

Author Comment

by:bignadad
Comment Utility
Portletpaul coups you please contact me via email?  I have some thing to run by you please.  Bignadad@gmail.com
0
 
LVL 8

Expert Comment

by:agillanders
Comment Utility
Umm. Access, SQL Server, Oracle, MySQL etc. etc. are all relational databases. So the question belongs in the forum for the engine being used. A SQL Server solution to this would not run in Access and vice versa. Especially as, with a normalized table design, it's probably going need Crosstab queries which are implemented very differently in the two engines.

The normalization is easy. The final solution depends on how the data is being sourced and what type of data structure fits the data. Based on some guesswork on the rquirements and assuming a list structure as the basis I suggest a modified linked list (see diagram).

But to get this right needs a little more information. Especially with respect to the end result needed. We haven't even discussed hiw AMI Part Number yet.

Example normalized link list structure
Adding the root part number will allow much more flexible querying since a group of related part numbers will share that without needing to walk the list. All that really remains is to fugure out how best to populate this table from his data source p he is already doing some preprocessing - this just varies that slightly to end with a normalized structure.

Alistair
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Bignadad, sorry but I believe this is contrary to the site policy - additionally you gain here by a variety of views from a diverse group of widely experienced experts.

My doubt about your table structure is that by having multiple columns for substitute part-numbers you by necessity then have to include each of those columns in a variety of queries - which in turn affects overall performance and complexity of the solution.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now