Setting up a table that works correctly with append query

[fortwrestler]

Right now I have 7 Lines(Fillers). I have these stored in a table under a single field (primary key).

Now, for each Line, there are multiple reasons which can cause the line to stop. These reasons can be duplicated among multiple lines, or they can be unique to a given line.

Right now, I have a table called Stop Reasons and in Field 1 it has the Line name (EQ5A, EQ5B, EH1, EH3, etc) and in field two it has reasons (Peroxide issues, infeed jams, caser, etc)....

So it looks like this

EQ5A Peroxide
EQ5A Infeed
EQ5A Caser
EQ5B Peroxide
EQ5B Infeed
EQ5B Caser
EH1 Infeed
EH1 Caser
EH3 Infeed
EH3 Caser
etc....


What I want to do is create a form that the user selects a Line and Entry Date, which then pulls from the above list with all given reasons possible for that line. Right now, I have used an append query and have it working, but it reverts back to alphabetical order versus staying in the same format I entered into the Stop reason table.

Is there a way to make the append query add rows with the same order as the stop reason and prevent it from autosorting alphabetically?

 

[pbaldy]

If you want things in a particular order, you have to have a field to sort on. Think of the table as a bucket full of data; you can't count on what order it might be in. I'd probably add a sort order field.

 

[jdraw]

Can you show us a jpg of your tables and relationships?

Sounds like 3 tables involved
ProdLine, StopCodeReason and since there could be many reasons for a LineStop

ProdLine --->LineStopReason<---StopCodeReason


LineStopReason would include some time/date stamp info

 

[fortwrestler]

Can you show us a jpg of your tables and relationships?

Sounds like 3 tables involved
ProdLine, StopCodeReason and since there could be many reasons for a LineStop

ProdLine --->LineStopReason<---StopCodeReason


LineStopReason would include some time/date stamp info

I'm not seeing the difference between LineStopReason and StopCodeReason .

Here is my two tables, which i then append to a larger summary table...
IE. A user inputs a line and date. Then they run the append query which goes through the Stop Reason table and returns all Stop reasons where the line field is equal to the inputted line.

 

[jdraw]

Perhaps there isn't. I had 3 tables.
You have a specific reason code per Line.
I had a table of Reason codes. My middle table was a junction of Line and Reason.

What if you add a new line? I was allowing for New Lines and Reasons.

 

[fortwrestler]

Due to the nature of the facility, this would rarely occur. But all i think I would need to do is add a new line to the constants, or add new reasons associated with correct line....

The reason I wanted the data to be sorted is because the way the data is tracked manually. That way, when it comes to data entry, one can just work their way down the sheet vs having to jump between rows to find correlating data.

 

[fortwrestler]

If you want things in a particular order, you have to have a field to sort on. Think of the table as a bucket full of data; you can't count on what order it might be in. I'd probably add a sort order field.

What about adding a numbered field (separate from ID), where I list each reason 1 - N. There would be duplicates in this field, but it wouldn't be hard to manage.

Could i return the reason using that number order?

 

[fortwrestler]

And, I pretty much answered my own question....thank both of you for getting my mind rolling....

Now to figure out how to get the form to display it in the correct order. It is still alphabetizing on the form, but not on the table.

 

[pbaldy]

That's what I was suggesting, yes.

 

[The_Doc_Man]

Here is what is going on, and it is derived from an "Old Programmer's Rule" - Access can't tell you anything you didn't tell it first.

In your case, that means that if you want things in a particular order there has to be something you told it (somewhere) to supply a value that, when sorted, results in putting things in that particular order.

It almost seems like a tautology to say it this way, but if you don't take control of your presentation order, you will have no control over your presentation order. That is why folks are telling you to have a field that contains presentation order. Think of it, if you will, like the property .TabOrder on a form with multiple controls. If you add the controls randomly, the tab order is the order of how you added them, not the left-to-right, top-to-bottom order associated with more conventional forms. You have to reorder the tab order while still in form design mode in order to take control of presentation order for the form's controls.

 

[fortwrestler]

Never heard that rule...Great thing to know....

I got it all working now