Restructure of existing (horrific) database

[rosearlene]

Hi all,

We have a database at work that tracks all of our work (we do forms), and about 6 years ago we were forced to restructure this db into tables that we KNEW wouldn't work. Well, it's 6 years later, and we've finally convinced the powers that be, that we need to re-do this db in order to get functionality.

I can go into details...but here's the scenario (using the "Northwinds" example). Let's say that we are a manufacturer, and we have a table that holds all of our customers. That table, SHOULD be the main table, and every order that the customer puts in, should all relate back to the customer IDKey. Then....for each order, there are multiple statuses.

Well, our db is set up that the Orders table is the main table, and the customer ID is but a field in that table. All of the statues for each order are related to the Order table, not the Customer.

Whenever we try to get the latest status of something, we end up getting duplicate records, because we see ALL of the orders from Customer XYZ (dating back to when the dinosaurs roamed the earth), instead of seeing Customer XYZ, and just the information for the latest order.

Is there an EASY way to change the structure so that the Customer table becomes primary, and all of the Orders AND their statuses match up correctly? At this point, we think we need to start fresh, and backload data.

Summary - there is a Customer table w/ unique IDs. The relationship for Customer to Orders is one-to-many; and then the relationship for Orders to Status is again....one-to-many.

We want to be able to pull a query that shows is all of the customers, their most recent order, and the most recent status of that order.

Any ideas? Or did I confuse everyone? ;-)

Oh! And I forgot the most important thing. The database itself has NO relationships. We had to use ASP runner to put it on our intranet, and all of the relationships exist in that program. So we basically have a bunch of unrelated tables.

 

[pbaldy]

I'm not clear on the structure, but customer ID shouldn't be anything more than a field in the orders table (that relates to the customer table). The statuses would be another table related one-to-many to the orders table, so it sounds like that is already correct. In other words, I see customers related one-to-many to the order table (a customer can have many orders).

From the sound of it, I'm not sure if the structure of the db is wrong, or just the user interface. It sounds like you want a form based on the customer table, with a subform to show the orders (or just the last order).

 

[rosearlene]

Thanks p - it's really hard to explain; but...we track all of our forms. Each form has a unique catalog number (CN) which is autogenerated. Years ago, with our original tracking database, each CN showed all of the work we did on each form (design new form, change form, translation, etc).

But, about 6 years ago we were forced to restructure, and we created a table called Work Request. So, whenever a user wants work on a form, they submit a work request. Each work request also gets an autonumber, and all work done on this request (status - i.e. cataloging, layout/formatting, translating, posted, etc) all relate to the work request itself. Not the catalog number.

If we do a query against the database, for each CN (catalog number) we get multiple records, because there are multiple work requests. What we NEED is to be able to get the most recent work request only, and the most recent status for that work request. We don't care about the history, we just want the most recent.

Sounds simple.....yet it can't be done. :-( From what I've been told, we would need to do programming so that each recordset runs through a loop, and it finds the most recent work request, and then goes to the status table, and finds the most recent status of that work request.

This is a nightmare, because we can't pull simple queries. I was asked for a list of all forms, by division - are they translated? And if so, what is the status of the translation.

I can't pull that information because I can't get one record per Catalog Number (I get all of the work requests, and have to manually find the most recent).

Confusing......I know :-(

 

[jdraw]

Can you post a jpg of your tables and relationships?

Seems part of your issue/problem is the reliance on Forms and some possible confusion on a WorkForm(Work Request) vs an Access Form - at least that's what I find confusing.
I'm surprised you've been able to work with the set up for 6 years...

 

[rosearlene]

Hi JDraw,

I'll do this on Tuesday (since I'll be away for the next few days). Right now, we don't have relationships in the actual database, because they are done in asp runner. The ORIGINAL reason we were forced to this was so our form liaisons could put in work requests on the intranet (instead of sending them to our mailbox). The IDEA was they could look up the status of any given request, instead of asking us. Of course, no one does that, they ask us! lol

We actually haven't been able to "use" it for the past 6 years. We keep copying down the database from our server, and do queries. However, as I mentioned, instead of getting one result per CN, we get multiple results (one for each work request), and then manually sort through to find the most recent. Yeah, it's a bear.

Thanks for the reply, and I'll post the info Tuesday.

 

[gemma-the-husky]

the hard thing about databases of this mature, is that the original developer may still be around, and there becomes an issue of sensitivity.

when you say "there are no relationships" between tables - i expect that is not strictly correct. just because there is not a formal design showing data connections between tables, does not mean that the dbs is not designed correctly with a normalised data structure.

 

[Simon_MT]

This looks like a bootom-up scenario.

Work Request (Should have DateStamp) > Catalogue > Client.

You should be able to have the Work Request Date and the Catalogue Date on Descending Order to find the most recent. Even better it the Work Request Status is Open or the Catalogue Status is Open then anyrhing else is ignored.

You could have two type of Forms - Open Catalogue items with a subForm of Oen Work Requests and/or Open Work Requests with the Client and Catalogue in the Header or Client with a SubForm of Catalogue Items or a tabulated Form with Both Open Catalogues and Open Work Requests. One Client per screen with the subforms handling the transaction details.

Simon