Solved Correct way to update data

You are still thinking narrowly. With a QUERY as your form's or report's data source, you can have the best of both worlds. Contemplate my long discussion in post #9 and the second query in post #13 that joins A and Query1. Do you not see that this gives you item data and most recent status in one place?

Let me ask another question: It has not seemed to be a problem so far from our end, but are you having trouble because to you, English is a second language? I am sensing that you are translating as you go - and I think you are doing a good job of it. But... is there another way for us to clarify what we are trying to say? I would hate to leave you confused because my words don't mean the same thing to you that they do to me.
 
Hi
Yes English is my second language...i speak 7 languages...
I understand what all of you are saying.. it is me I just do not know how to make that with one or 2 querys?
I have one primary table and then 3 transacion tables ( 3 diferent systems which can use the item, but one item only one system) ... and I dont get what I want with one/2 querys....
i just do not know how to make query with multiple tables?
I have to say that I have status field in all 4 tables ( one primary table and 3 transcacion tables)
I make a query and I have duplicate ID and then 3 diferents statuses....
is just me and sorry for my english and if i complicate all that
can someone show me how to create query with multiple tables? some example so i can study what i am doing wrong? my PK and FK? ohh
:unsure:
 
My 2nd query example from post #13 shows a two-source JOIN. The general form of a two-table query for MOST (not all) applications is:

Code:
SELECT A.Afield1, A.Afield2, ..., B.Bfield1, B.BField2, ... FROM A INNER JOIN B ON A.Afield1 = B.Bfield1 WHERE .... ORDER BY ... <<<other options>>>;

You have fields in A and B that are related by some common field, which is Afield1 and Bfield1 in this example You take fields you want from A and fields from B, qualifying their locations with the A or B prefixes as shown. I used an INNER JOIN but there are other options. INNER JOIN is the easiest for starters. The JOIN occurs on two fields, one from A and one from B, that must match in order to say they are related. Note that depending on the exact nature of the relationship, it would be possible to have multiple matches in one table matched to a single field in the other table. If so, you will get as many records in the query as whichever one has the multiple matches. You will get every combination.

So for your original question, you have one item record with a unique item ID and you had the possibilities of multiple action records starting from NEW but including IN USE or RETURNED or IN REPAIR or whatever is appropriate to the item. You get this because the rules of Access are all about combinations. When you have a single-table there are no combinations. When you have a multi-table query, Access will give you combinations and permutations like crazy - unless you limit them with ON clauses and WHERE clauses.

I asked about your English to verify that we were not giving YOU a language problem. If you believe your problem simply comes from a lack of full understanding of Access, then we know that the miscommunication isn't due to our being careless. I think you are doing fine with language. But if you are not comfortable with queries, that is a different problem.

I would advise you to study queries because in Access, they are the workhorses. Tables do their part, of course, but they just store data. They can, of course, be targets of INSERT INTO, UPDATE, or DELETE actions, but otherwise they just lie there, sitting around doing nothing. Queries can actually DO things to data. They are (as we say in the USA) "worth their weight in gold."

The thing to know about queries is that you can drive many things from them. A query can reference another query (e.g. my 2nd query in post #13 references an earlier query.) They can be record sources for forms or reports, although with reports sometimes Access gets a little stubborn. VBA Recordsets can work with queries quite easily. Queries are everywhere in Access. Therefore, do some reading so you can make queries become your friend.
 
I have one primary table = table A , here is the basic info of the item and here starts everything
then this item from table A can be used on 3 different systems ( imagine: ford, toyota, ferrari) this mean 3 different tables? ( table B ford , table C toyota, table D ferrrari)
when user start entering data in table A he choose which system this item will use, once the item has the system then this item can be used only on one system, if you choose for item 1 ford then this item 1 is used only on ford and all the data for the usage will go to the table B ford,if you choose for item 4 toyota then this item 4 is used only on toyota and all the data for the usage will go to the table C toyota, if you choose for item 5 ferrari then this item 5 is used only on ferrari and all the data for the usage will go to the table D ferrari,
I need to have separate evidence of every sytem ( imagine: ford, toyota, ferrari), becouse every system have some different fields and also becouse some system must be released , if the system is not relased you can not use the item for usage, in ford you have to calculate how many times the item was used, in toyota you have to write which oil is used and so on..

and becouse of that every table has status, becouse ford can be relased and toyota not, if ford is not relased you can not use it....

maybe i create that wrong? hmmm :oops:

and yes i will study querys..i know how to make simple query but multiple tables is strange i dont get data that i want...
 
then this item from table A can be used on 3 different systems ( imagine: ford, toyota, ferrari) this mean 3 different tables? ( table B ford , table C toyota, table D ferrrari)
No, it does not mean 3 different tables. It means one table that includes a field that SAYS Ford, Toyota, Ferrari. And you use queries if you want to see ONLY Fords or ONLY Toyotas or ONLY Ferraris.

The usage table DOESN'T CARE what it is because the item definition table DOES care. All that matters is that your status would be kept in the usage table NO MATTER WHAT the system happens to be. Is there that much difference between these systems for the USAGE table?

Now, as to different fields for different systems... Really? Are we talking about a LOT of fields or just a couple? Because there is nothing wrong with the idea that a given field will be blank for some things and not blank for others. Splitting out these different fields IS done sometimes, but not unless there is a very large number of totally unique fields that differ from system to system.

I'm beginning to think that at least part of your problem is NORMALIZATION. Have you studied that? It is crucial to having a good design. If you need to study that, here is my advice. You CAN search this forum for articles on normalization since this is a database forum. You can ALSO search the web for articles on DATABASE NORMALIZATION. You need to qualify it for a general web search because normalization is also used in chemistry, politics, diplomacy, mathematics, and medicine. If you go the web route, you might find articles in your preferred language, but in any case I would advise FIRST looking at articles from an .EDU domain, usually from colleges. They will focus on the academic side of the discussion and are less likely to try to sell you something. Once you get the idea, if you wanted to look at a couple of .COM domain entries, that is OK. They will just want to tell you about THEIR version of SQL or THEIR development environment.

The point of NORMALIZATION is to try to maintain purity of purpose for a given table. If a table describes items, it should not describe people or places. (That's a simplified way of looking at it.) It should also not describe ACTIONS even if those actions are related to the items, particularly if keeping a time-based history of actions is important.

Your question about "3 tables for 3 different kinds of systems" describes a situation that violates normalization by making a division of data where one should not be. If we use the Ford, Toyota, Ferrari model, then your top level table is really about automobiles. The table can SAY what kind of automobile it is, but it is always about automobiles. Ford, Toyota, and Ferrari all represent DATA in the automobile table, not separate tables.
 
Hello
Thank you for your explanation.
Can we talk here about normalization? I will also study that.

ok so you mean that I have to create 3 separate fields? ford, toyota, ferrari and all that in one table?table B?ok

now i have one field called system type and then here user choose which system he will use for the item. ( toyota, ford, ferrari)

ok. i create that in separate tables becouse every system has then different status?
so imagine:
item 1 system ford status relased
now i know that ford is relased and toyota and ferrari are still status new
if i put separate field and all in one table how i will know who is relased? toyota? ford? or ferrari? now i have one status in one table and from that i know that ford is relased.
if i put all 3 field in the same table and i have only one field status how i will know who is relased? i will have to create 3 statuses?
i will have mess? i will have one field filled and two fields empty? status for ford be relased and for toyota and ferrari new? users will be confused?
hmmmm
 
Normalization is the process that lets you look at an object and say, what are its properties? I.e. how do I describe it? The item's record needs a unique and permanent identifier, which in this discussion will be AID. The other properties might not be unique, but the important takeaway is that one record describes one example of whatever it is that the table represents. We are saying, for discussion, that the table represents cars. So each record in A is one car.

Let's say you have your table A (describing cars) with fields like this:

A.AID - autonumber, LONG integer. Think of the name as "A's ID" but just shorten it so you type less. And besides, apostrophes don't work in field names very well. Using "ID" as the name would work but has no hint (to a casual reader) as to "an ID ... of what?"
A.AName - some kind of name for an object described in table A. This is "A's Name" but again, we shorten it. But we don't shorten it down to "Name" because that is a reserved word.
A.AType - text, for which the expected values will be Ford, Toyota, Ferrarri. "A's Type" but "Type" is another reserved word...
A.ABody - text, might be COUPE or SEDAN or SUV or PICKUP or 18-WHEELER-TRACTOR or FARM TRACTOR or GOLF CART ...
A.AColor - text, might be RED or BLUE or GREEN or FUSCIA or PUCE or UGLY BROWN or ...
A.....whatever else - but NOT the status.

When you want to work only on Fords, your query will include "WHERE A.AType = 'Ford' AND AID = {some number} AND ..." - but here is the important part. You don't pick a record purely because it is a Ford. You pick an item because of its AID - which HAPPENS to be a Ford. AND if it happens in that little WHERE clause that the AID doesn't select a Ford, you would get back nothing.

Note that if someday, someone sneaks in a Lexus, you merely type in "Lexus" and the rest of the car description. That is ALL you have to do - except for those things you think are unique.

Why isn't the status in A? Because unlike the properties shown in A, the status of the item changes based on an ACTION. Table A doesn't describe actions. It describes cars.

You want to track what happens to the cars. Based on "purity of purpose" (or perhaps you might prefer "purity of representation"), table A does NOT track actions. So you need a new table, called B in this example, to track what happens to the cars. You associate the actions to the right vehicle by tagging B records with the correct AID as a FOREIGN key, a pointer back to the object - the car - for which the action occurred. You track properties of the actions in the action table, B. Because records in B match up to a record in A, and can't exist if there IS no record in A to act as a reference, we say that A is the independent table and B is the dependent table. You will also see A as the parent and B as the child based on the preferences of whoever is writing.

In your B table, you have
B.AID - LONG integer, foreign key
B.BDate - date of most recent transaction. "B's Date" but "Date" is a reserved word... (by now I'm sure you get the idea.)
B.BStatus - text, status of most recent transaction
B.BWho - text, name of person performing the transaction
B.BMileage - number, mileage on the system as of the BDate
B.....whatever else describes the action.

That query we talked about earlier would JOIN A and B on the AID field. You can get information about the object from A and about the most recent action from B using that JOIN. If you use the matching AID record with the highest date (MAX(BDate)) you will know the current status of the item referenced by the number in AID. In the B table with a WHERE clause for a given AID, you would have the chronological history of status changes for that item selected by AID.

Think about what this would look like in a spreadsheet. Half the row would be about the car; the other half would be about an action. But if the next entry is about the same AID, half the next row would repeat the information about the car; the other half would be a new action. By splitting the tables (part of the normalization process), you isolate static vs. changing descriptions. This takes up less space and makes it easier to work with the parts.

Here is the reason you have to split the tables when normalizing. There is a rule that in a table, every property must depend uniquely on the prime key (in our example, AID). The Color and Type and Body depend on the AID. No problem there. BUT the status depends on AID and the date because when someone takes an action the status changes - based on the AID AND THE DATE! So leaving the status in the A table violates the property-dependence rule. Doesn't matter if you don't store the date. Whether it is stored or not, the status depends on more than just the AID field. And that's how you know you need another table. Talking again about spreadsheets, when you see yourself having to repeat data already in a previous row, that is a sign that you need to split the table when you convert the spreadsheet to Access.

This is the start of normalization. There is SO much more to discuss, but this gives you the idea of HOW you look at your data. I would strongly advise that you do some reading before you drive yourself crazy. (Note that such an action would be in the B table...)
 
Last edited:
Helloooo
yes I think now I understand what I make wrong. I make wrong my design. I should never put the status in first table, table A. This was my big error.

this opens my eyes
The_Doc_Man
Why isn't the status in A? Because unlike the properties shown in A, the status of the item changes based on an ACTION. Table A doesn't describe actions. It describes cars.

and this opens my eyes
Pat Hartman:
You need to take off your spreadsheet hat and put on your relational database hat. You are visualizing columns with the names of data value rather than rows with a generic column name but whose value is a piece of data that represents the name of an automobile maker.

I didnt know that.
Thank you very much The_Doc_Man and Pat Hartman to write so good explanation so I could understand. I think if that read a little child 5 years old he will understand. So nice explanation with examples. Thank you.

One question: it is better then to make?
1. table A for items tbltems
2. table B for the usage tblUsage
3. table c with the statuses? tblStatus

or should I put the statuses in table B and no table C?
thanks again
 
One question: it is better then to make?
1. table A for items tbltems
2. table B for the usage tblUsage
3. table c with the statuses? tblStatus

or should I put the statuses in table B and no table C?

This becomes difficult to answer. Like Pat says, your tables will be based off of YOUR requirements. We cannot see your true requirements and thus cannot know the answer. In ALL database design projects, you must map the data to the real-life process, subject to normalization analysis. (And please note I gave you only the START of normalization concepts.)

From our discussion so far, your statuses only change as the result of some type of usage activity. Therefore, I would say "no table C" - but I don't know everything you want to do so understand that the answer could be that you DO need that table. We cannot advise you beyond the need to do a normalization exercise. That example I showed you about key dependency analysis is a good test for whether you need a new table or can use the existing table.

Note also that in practice, you might have SEVERAL tables related to what we discussed in order to drive either a list box or combo box. For example, you can have a "brand name" table for which the records are "Ford" and "Toyota" and "Ferrari" so you can use a combo to select that value. I jokingly listed some colors, and those could go into another table that would be used to drive a different combo. Then there were the body types, which could lead to another table to drive a third different combo. This is a case where what you wanted to do with the data would drive its form-factor. If you wanted to use combo-boxes to minimize errors (by only allowing folks to select available choices) then you would turn those options into a table of options. It's up to you as to what you want it to do and how you want to do it.
 
Ok so my story is next:

I am making database of some items used in pharmacy and chemistry.
I will have 4 different type of items. ( imagine: chair, mouse, window,pencils)

Items will be conected on different systems. I will have for now 3 different systems. ( ford, toyota, bmw). For now i have that systems in one field called system type and then user choose from combox which system he will use for that item? is that correct?

I must collect the data for the item, his usage and status, that mean for now 3 tables for one type of the item.?
tbl1 tblChair
tbl2 tblChairUsage
tbl3 tblChairStatus

and then also 3 tables for window? 3 tables for mouse? 3 tables for pencils?
hmmm i hope i am thinkking correct now. I need to have separated items becouse they are different. ?

i will have 6 different statuses of the item. ( new, in use, relased, sended, out of use, destroyed)

the story goes now:
user enter the item into database = tblChair
he will use some IDChair and make some usage ( here is going tblChairUsage), he will write what analyis he make on that chair, what liquid he use, when he did that, which notebook he used, what page, and then he will marked the status for the item. if he will make relaese analysis ( purpose of the usage) and if the item will pass he will say status relased, if item will not passed he will say in use... if he will change the status he will change the purpose of the item and he will say changing status and he will change the status to sended, so the user will know exatly if the item is relased or not if is sended or not.

I already have everything done. My problem was only that update status if I am doing that correct or not.
Now like i learn from here I will make some changes in database.
in first table i will delete the status field and I will make new table status.
I have to be careful what ID and PK i will chosee so then i will have correct relationship?
 
This is getting a bit abstracted and harder to follow than it needs to be. Instead of tables and chairs and Fords and Toyotas, try using real category names. For what it's worth, I'm a PhD chemist and would understand analysis terms a LOT better than you might have first imagined.

The bit about using a combo box is, if you wanted to minimize the chance for someone to pick the wrong system or object or whatever, you make a list in a table and then can build a combo box to store either (a) the actual name or (b) a code associated with the actual name. Like we used to say about FORD, that was the coded short form of Fix, Overhaul, or Repair Daily.

I have to be careful what ID and PK i will chosee so then i will have correct relationship?

Absolutely true. In a relational database (like Access) you establish relationships between two tables by having a primary key in an independent table and a foreign key in a dependent table. So let's go back to your original "cars" and "actions"

You create a Car record and its AID is 1. Now to show that car #1 is new, you create an Action record with AID = 1 (to select Car #1) and action or status "NEW" to show that it is new and you include today's date to show WHEN it was new.

Sometime later, you check out Car #1 for inspection. So you create an Action record with AID = 1 (to again select Car #1) and action or status code "INSPECTION" plus the date. Now you know the status of Car #1 is that it has been inspected.

So in this simplified example, you relate the car to its status history by having the AID field in the Cars table and the same value in the foreign key field of the Actions table. That's how Access knows they are related - because they have a matching PK and FK.

Switching gears, it looks to me like you have a staging situation where a sample comes in and has a series of steps to go through from being NEW to being done, and from this viewpoint, it is a one-way street. Once you are no longer NEW you cannot go back to that status. And when you reach status DESTROYED then you are permanently done with that record (other than for its use in historical analysis such as summary reports). Is that closer to what you are really doing?
 
Switching gears, it looks to me like you have a staging situation where a sample comes in and has a series of steps to go through from being NEW to being done, and from this viewpoint, it is a one-way street. Once you are no longer NEW you cannot go back to that status. And when you reach status DESTROYED then you are permanently done with that record (other than for its use in historical analysis such as summary reports). Is that closer to what you are really doing?

Yes. That exatly is the goal of my database. When the item will be destroyed some day, user can not choose it and can not used for analysis.
I hope now I am in correct way with my plan.

I am enginner of chemistry and pharmaceutical tehnican. :) :giggle: and you are Phd Chemist very niceeeee. :)
 
We have at least some technical language in common, then. As a bit of advice, switch to calling things by their real (or at least approximate) names. You might be surprised at how well we can do even without totally abstracted names of classes, objects, and categories.

And yes, the "Doc" in my screen name reflects my degree.
 
Hi again
I have a new problem.
Finally I managed to have correct updating status method. A lot of testing shows me that I have again new problem.
From you experts I learn and create a query that update my statuses. This works perfectly.

Like Arnelgp suggested me I have on Unload event on my form(frmUsage) the next code
Private Sub Form_Unload(Cancel As Integer)

CurrentDb.Execute "Update tblKolone2 Set StatusKolone = DLookup('LastOfStatus','qryItemStatusPrvaStran','KID = ' & [KID])"
CurrentDb.Execute "Update tblKolone2 Set StatusKolone = DLookup('LastOfOddelekLastnik','qryItemStatusPrvaStran','KID = ' & [KID])"
End Sub

Problem is how to manage now to how the primary status? How to revert status back?

When users first enter the item in database the item have status New. ( tblKolone2)
Then user go to the second form frmUsage and want to make some analysis. He start entering data and suddenly he say I will close the form. He did not enter data in status field. Here is now status field empty. he close the form.
Becouse here I have the code on unload event my status who was in the begining New go to empty field.

I need to show again status New if user dont fill all the fields and decide to close the form without saving.

I need to put new query on the Undo event form? Here I think Arnel also show me some example but I dont know I dont get the correct status back?
I try it but doesnt work?
hmm
what is best to do?
thank you
 
Your pair of .Execute statements updates the same field - StatusKolone - to two different things, so the first update does nothing permanent. You could omit that statement and never see a difference in effect. That was, however, a comment in passing.

The REAL issue is that it sounds from your description that you updated the status away from NEW prematurely. You should NEVER allow a status to change until you know you have everything you need. Updating on-the-fly is dangerous because of the risk of connection losses, crashes, and people who incorrectly follow procedures. Before you allow the form to unload, you need to check whether the current record is self-consistent. That second form should decide if IT has enough to correctly update the record. If it does not, don't allow the update but instead use a Me.Undo in the Form_BeforeUpdate event. Don't allow the form to exit without either committing the change or canceling it (i.e. UNDO).

Remember that with event code in the second form, even though that form might be bound to a child table, you can run one of those CurrentDB.Execute statements to update the main record's status in the main table. And opening the second form is no biggie because the KID for the implied new record's foreign key is available, and HAS been available since you created the NEW record.

There is something to be said for having forms doing only limited things in order to keep each task relatively simple. There is a contrary philosophy that the details form would - at that moment of making the detailed updates - also know the correct status for the main form. And the philosophy there is "make the change where you know it will be good."

I understand that you have a complex problem. Getting it right can be like walking barefoot across a floor full of broken glass. No matter which way you go, things seem perilous. We've been there. It may not be helpful for meeting deadlines, but the only true solution is to pick your way slowly and carefully so you can avoid getting stuck. There is no substitute for careful analysis, no matter HOW much we would want to get the job done quickly.
 
Hi DocMan
Yes secod update statment is writen wrong, I was playing here and trying to create query who will show me first value entered in the table? And I didn managed nothing.. :confused:


Yes I know I have complex problem. When I start thinking ohh yees I finally managed correct status then I start testing and do every possible changes I notice ohh that is not good. If user will close the form without the saving record my status will not be correct.
If the user enters all the data in the form and close it then I have the correct status.
Having the correct status was my biggest problem. I was using differing approach using do sql. Now I am using query for that.

So thank you for you explanation. Now reading your explanation make sense how to do that.
Here bellow is my code on my forms before update event. So you mean I have to us Me.Undo here inside? Where exatly I have to put?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If SaveYN = True Then
SaveYN = False
Else
Cancel = True
MsgBox "Kliknite na Shrani in shranite zapis.", vbOKOnly
Exit Sub
End If

If ValidateForm(Me, "required") = False Then
Cancel = True
End If

Call AuditData(Me)
If Me.NewRecord Then
Call AuditChanges("UporabaKID", "New")
Else
Call AuditChanges("UporabaKID", "Edit")
End If
End Sub
 
You have the option (with Form_Unload AND/OR with Form_BeforeUpdate) to set CANCEL=TRUE to prevent an update from occurring, either by a SAVE button or navigation away from a record (which does an automatic update) or a form closure (which does an automatic update). At the point where you do a CANCEL=TRUE, the update or unload event will not proceed. The form remains open as though the user action that triggered the event never happened. At that time, you have a chance to do something and a choice of what to do.

You CAN just say "continue to fill in blanks to finish the operation" and then when your user tries to save or close the form, eventually the validation code could say "OK, this is acceptable" - at which moment you would do all of the primary and secondary update actions discussed earlier like updating the details and updating the main-table status.

OR you can say "If you are going to close the form in an incomplete state then I will not allow this to be saved." At which time you would do the UNDO and then DON'T set CANCEL for the Form_Unload. If the triggering event was the Before_Update but an UNLOAD wasn't pending, then it might be more of a puzzle as to what to do.

When I run into this problem, what I do is make it impossible to close the form using any of the standard Access methods. What I do is build a CLOSE button that sets a software flag. Then in the Form_Unload, if the flag isn't set, you can't close the form and none of the side effects of closure can occur. The CLOSE button also checks for whether the form is currently dirty and can issue a message warning the user of unsaved data.

I also use a SAVE button and flag so that the Before_Update can know that update event this wasn't due to an implied navigational save. AND you can put validation code under the SAVE button rather than the Before_Update event, so you don't even TRIGGER the save until it passes validation. In essence, this is the philosophy of "get in their way when they make a mistake so they have the chance to fix their mistake." The final piece of that puzzle is, if I am going to make them manually save the record, allow them the option to undo what they have done by adding an UNDO button. That does a Me.Undo on everything so that there is nothing to be saved - which prevents an Access automatic save from occurring.
 
hmmmm I will get lost?

I have a save button, close button
I have to use cancel= true or i have to use Me.undo? and where is better to put? unload or before update? I know you say I have two options but where is good to use it? where experts you use the code?
Ohh I am so sorry if I am stupid I am just in mess now :unsure:

Can you show me some example how I have to do that?
 
You have two options and can choose either. It wasn't intended that you would handle both options, but rather that you could pick the way you wanted to handle it. But I can't answer your question because this is your project. You have the choice of blocking the closure of the form or of just discarding incomplete records. But until you decide how you want to treat this in practical terms, the code will make no difference.

From this question, I think it is time for me to unload some "Old Programmer" proverbs.

1. If you can't do it on paper, you can't do it in Access.

In any project, you have to have goals. Usually, people lay out their goals in a design document or specification. You are building a program to track the actions of your business. In order to do that, you must intimately understand your business flow. You must know the data elements (entities) of your business, whether we are talking employees, clients, sellers, buildings, equipment, sales, purchases... WHATEVER is part of your business. You must know the intimate details of how those things are used. Many of your tables will represent those things. Whatever is done in your business, you must know how it flows.

How you lay out your goals is up to you. But the general approach is to identify the entities you will track whether concrete or abstract. You identify how these entities interact. You lay out your desired outputs. In essence, you must build a roadmap of how you get from inputs to outputs by all of the processes in between. You answer the questions of "how will I do that step?" by looking at the step in the real world and deciding how to perform that step (or record it) in the computer. When you are done, you have a document that should answer all of your "what happens to this entity at this phase of my business?" At that point you have a roadmap. And if you are going somewhere without a map, how in the world will you ever know you have arrived?

2. Access won't tell you anything you didn't tell it first, or at least tell it how to find it for you.

Let's start by understanding that Access is dumber than a box of rocks with regard to the real world. All it knows is data manipulation and making pretty forms and reports. YOU are the subject matter expert. Access cannot learn anything - but you CAN write code to show it how to do some step or another. You have outputs. Access can only tell you those outputs if you make the required inputs available. Sometimes, it becomes necessary to look at an output and work backwards through that map to verify that you will have the inputs you need to produce that output. That is, if you want X, Y, and Z as outputs, you need inputs for X, Y, and Z. AND if you want XYZ as output, you need X, Y, and Z and you ALSO need the formula that mixes them together. Consider this a type of "procedure checking" perhaps. Just remember, you are the one who must make stuff available for Access. It is an ORGANIZING and COMPUTING tool. Not a manufacturing tool.

3. Access can never tell you how to do your business. Only YOU know how to do your business.

People want Access to help them run their business. Yes, it will do that... if you tell it how you run your business. Access will not make a decision for you that you wouldn't have made. This is an important concept.

In English, we have a phrase that we use: The tail is wagging the dog. The Access equivalent would be if your application told you how to run your business in a way that was contrary to the intent of the business, but you let an inanimate object guide your business. If you build an application for your business but it is an imperfect representation, you could find yourself in real trouble if you followed it down your garden path. Remember, except for true AI cases, computers don't come up with new things. Their strength isn't based on creativity, but rather that they can do things QUICKLY and ACCURATELY. But give them a bad formula and they can still be wrong.

When I worked with the U.S. Navy, the biggest project (for which I was a systems administrator for the host system) was a personnel management system. They modeled the heck out of personnel transactions such as addition, separation, transfer, promotion, demotion, training, etc. We gave the bosses at BUPERS all the details they needed to make decisions. We had models for every decision they could possibly make. But THEY made the decisions based on what our DECISION SUPPORT system showed them. They ran the show. We just tracked their choices and helped them push the appropriate orders to the appropriate channels. Our machine never told them what to do, though it sometimes told them what they couldn't do (if, for example, no one was eligible for a particular assignment with requirements). The point is, whatever you design has to support how your business is supposed to run. Back to the map analogy... just remember that the app your are building is the map; the actual business is the territory. Be sure they match up.
 
The status belongs in one place only, and that's part of your data analysis

if you have an order, and each order has several lines, then the status probably belongs in the order, but it depends how you deliver and manage the order.

So you might have statuses for 1, New Order, 4, Final Order, 5, Despatched Order, 6, Completed Order, 7, Invoiced, 9, Cancelled
However if an order comprises multiple deliveries, the status (or statuses) management will be different. The status of the order will not be complete until all the deliveries have been made, but each delivery will have its own status independent of the order. You still need to aim for one status per process, and don't duplicate volatile data.

I would also have a numeric status, not a text. If you ever want to change the text, it's much easier with a numeric value. The numbers will generally update in sequence, as illustrated above, but not necessarily, depending on your exact process. Maybe number the statuses 10, 20, 30 etc, if you think you might even need to add more statuses in between these values.
 

Users who are viewing this thread

Back
Top Bottom