Automaticly Sort without my permission

[mrtool]

Goodmorning everyone..

My problem is as following:

I have input data in a table. By the time i closed it an open it back. Access have change the position of the data and put them in a sort. And it keeps on doing it. But the thing is that I dont want access to sort it, I want it to be exactly how I input it. Is it possible that Access could leave the data exactly how I input them and not sort them??

Is there a key that I have to switch off or something???

 

[Scooterbug]

A couple of questions...

1. Why is it important to keep the data in the order that you input it? Tables should only be used for storing data...how they are stored in there should be irrelevant.

2. I suspect that you do not have a primary key. If you were using an autonumber for a primary key, then the table would keep them sorted by when they were inputed.

 

[mrtool]

Ok I got through with that..i just make an extra field and I enter numbers and sort that field, that way i could maintain my input in the order i set it.
But I'm still in problem because I have the input that i enter in a drop down in another in another field, but even though I have it in the order I want in the table, access still puts it in it's way in the drop down list..
And that's my problem because the drop down have to be in the order i have put the info in..

 

[mrtool]

My reply to scooterbug:

As primary key I have the field that I dont want Acces to change. Because I conclude that a drop down box would only give you the data you have as primary key..

Let me gave you a lil more info..
The data that I input that I don't want access to change are the date and it's week.
date 2Datum1 - 5 June (Week 23)8 - 12 June (Week 24)15 - 19 June (Week 25)22 - 26 June (Week 26)29 June - 3 July (Week 27)6 - 10 July (Week 28)13 - 17 July (Week 29)20 - 24 July (Week 30)27 - 31 July (Week 31)3 - 7 Aug (Week 32)10 - 14 Aug (Week 33)17 - 21 Aug (Week 34)24 - 28 Aug (Week 35)31 Aug - 4 Sep (Week 36)7 - 11 Sep (Week 37)14 - 18 Sep (Week 38)21 - 25 Sep (Week 39)28 Sep - 2 Oct (Week 40)5 - 9 Oct (Week 41)12 - 16 Oct (Week 42)19 - 23 Oct (Week 43)26 - 30 Oct (Week 44)2 - 6 Nov (Week 45)9 - 13 Nov (Week 46)16 - 20 Nov (Week 47)23 - 27 Nov (Week 48)30 Nov - 1 Dec (Week 49)7 - 11 Dec (Week 50)14 - 18 Dec (Week 51)21 - 25 Dec ( Week 52)28 - 1 Jan (Week 53)
I input it like that..but Access suddenly changes it after closing the table into
Date (IT)Date1 - 5 June (Week 23)10 - 14 Aug (Week 33)12 - 16 Oct (Week 42)13 - 17 July (Week 29)14 - 18 Dec (Week 51)14 - 18 Sep (Week 38)15 - 19 June (Week 25)16 - 20 Nov (Week 47)17 - 21 Aug (Week 34)19 - 23 Oct (Week 43)2 - 6 Nov (Week 45)20 - 24 July (Week 30)21 - 25 Dec ( Week 52)21 - 25 Sep (Week 39)22 - 26 June (Week 26)23 - 27 Nov (Week 48)24 - 28 Aug (Week 35)26 - 30 Oct (Week 44)27 - 31 July (Week 31)28 - 1 Jan (Week 53)28 Sep - 2 Oct (Week 40)29 June - 3 July (Week 27)3 - 7 Aug (Week 32)30 Nov - 1 Dec (Week 49)31 Aug - 4 Sep (Week 36)5 - 9 Oct (Week 41)6 - 10 July (Week 28)7 - 11 Dec (Week 50)7 - 11 Sep (Week 37)8 - 12 June (Week 24)9 - 13 Nov (Week 46)
And that I dont want it to do. I want the weeks to stay in order

 

[SOS]

Access does not store data in tables in any particular order. When you open a table you are actually looking at a query of the table.

You should use queries for sorting and do not rely on using tables directly for their sorting, it will fail at some point. Queries are your friend, they are the workhorse of Access.

 

[Scooterbug]

there is an easier way to find the week of the year. A simple formatting of a date can do that for you.

WeekofTheYear = format("[YouDateHere]","ww")

And the reason why it's sorting it by the first number of the field is that it has to sort it somehow. I'm guessing that you dont have a primary key for the week entries, so it sorts it by the first character of the entries you entered.

 

[Kryst51]

Also, a primary key should be an auto number, don't try to keep up with numbering records youself. I suggest looking up some beginner articles on google or training on microsoft, thre is so much that goes into table design that it will be impossible for you to continue without learning some basic thought processes, funtions, definitions, etc. I suggest learning about tables and relationships between tables first. Using Access is a different mentality then using a spreadsheet. As Scooterbug and SOS said queries are used for sorting the data, but I don't think that you know enough yet to use them. Good luck with everything, I spent a year building and rebuilding my fist database, learning about everything, so it does take time.

 

[SOS]

Some good Access tutorials are at http://www.functionx.com

 

[The_Doc_Man]

MrTool:

SOS said it correctly, but I want to amplify it.

When you look at a table, you aren't really looking at a table. You are looking at a default (template) query in datasheet mode for that table. When you click on a column to sort it, you are implicitly sorting the query. But since this is a protected template query, you cannot save it. (And in fact, despite what you might think, you don't really WANT to save it.)

The table always has whatever order it has, and it will always have that order, which is generally in the order each record was created. The ONLY way to impose a default order on a table is to establish a primary key for it, then do a database compaction. All tables having PKs are written to the compacted database in PK order.

Access is not sorting the table out from under you after you sorted what you thought was the table. You never really sorted the table. And except as noted above, never will.

As to what to use for a PK, search this forum for the topic "Meaningful keys" and its partner term, "Meaningless keys." There was a spirited discussion about key selection some time ago using those key phrases.

The drop-downs will give you an option to sort if and only if there is a reasonable way to implement it. Like having a viable PK. If your records can be created in random order then you need a meaningful PK to re-impose your desired order.

 

[gemma-the-husky]

I have input data in a table. By the time i closed it an open it back. Access have change the position of the data and put them in a sort. And it keeps on doing it. But the thing is that I dont want access to sort it, I want it to be exactly how I input it. Is it possible that Access could leave the data exactly how I input them and not sort them??

perhaps THIS is the issue - you should NOT be entering data directly into the table Instead design a form based on the table (more correctly based on a query using the table). Then you can apply suitable sorting to your query, and you wont have the problem.

As everyone has already pointed out, a table has strictly no order - indeed relational databases do not intrinsically deal with order - you just manage and process sets of data - to which you can arbitrarily assign a temporary order.

Hence any normal acess action query just processes the ENTIRE data set in the same way, and therefore is order independent

 

[mrtool]

I have a feeling that most of y'all answer are for the second step, which is the queries. But i didn't reach to that part yet.
I'm still by the input of the date in the tables. The thing is that is not me will be putting the data in in the future. My tasks is to just presentate the database. So right now I'm just working on ways to facilitate the job of the ones who will be inputting the data in the table.

The name of 1 table is Project info. This contains of several fields. Including the Begin date & End Date. For these fields I have a drop down list facitility that way the ones who are filling it dont have to look up for the date and the week.. So I filled it in myself (as you can see already above) tthat way they would only have to choose..but the order changed..

So peeps especially (kryst51) since you say I need to look up for beginner articles and so on...I suppose you read them already. So how would you do it?? To have the date and the week in drop down that way the users would only have to select and without access changing the order..

 

[mrtool]

gemma the husky...
I wanted to do that at first..but due to other reasons I chose to make it possible to input the data from the tables..

It's either you input your data through tables or through forms right??

 

[SOS]

1. You don't use lookups at table level (which it appears you have set up). You use them at form level. So after creating your form for input, remove the lookups at table level.

2. You use forms for users to interface with the data. Do not use tables or queries directly as you have no control (or very little) over what goes in and when.

 

[Kryst51]

So peeps especially (kryst51) since you say I need to look up for beginner articles and so on...I suppose you read them already. So how would you do it?? To have the date and the week in drop down that way the users would only have to select and without access changing the order..

Thanks for the props!

Unfortunately that is the problem, the tables need to be well thought out The issue of selecting dates and such, a form needs to be built with the combo boxes for your dates, which you won't be able to build the forms correctly unless you understand how tables work. There is no easy answer to give you. Access is not a program like word or excel where you can just start typing and be able to produce something that is workable. It's just more complicated then that, and takes more time (Meaning your problem won't get solved quickly). Check out the site that SOS recommended, do a little research into database table design and relationships. And if you have questions about what you are reading, I know, at least, that I do not mind answering more questions.

 

[SOS]

Here are some good reference materials:

Database Normalization

Free Access Tutorials

Free Access Tutorials (#2) for 2007

Free Access Tutorials (#3) for 2003

Free Access VBA Tutorials - Access 2007

Free Access VBA Tutorials - Access 2003

 

[mrtool]

Can y'all help me out making a database of a staff planning (in access) all over again.

Or maybe y'all know about 1 already on the net where i could download it adjust my changess in to it..Can y'all help me out with that??

 

[Kryst51]

Check out the mocrosoft office site for free templates.... I still strongly recommend that you spend time trying to learn more, it is very beneficial.

 

[mrtool]

I have been looking..but I haven't find 1 that fits best for the assignment I got.. Is there another site that you know about..

Or isn't it possibel that I just send you the file to see what I have been doing and what I'm trying to explain to you..And by giving me feedback looking at it because all your doing is telling me to go learn the basics..without knowing that I already looked at the basics, and that I already have something made up..So looking at the basic are only illustrating me stuff I already know about without seeing where exactly my problem is..Could you look at it..and maybe understand the central of my problem..and point me out more exactly what to do to solution that problem instead of sending back to stuff that I already know..without seeing my mistake self

Is it possible??

 

[Kryst51]

Sure, post your database, and I'll take a look.... But your questions really do sound like you don't understand the basics..... Which isn't a problem, but one thing I learned about forums, yesterday as I was reading up on etiquette, is that people generally don't like doing the research and work that the questioner should be doing. This being said, maybe your first questions need to be about what your reading, if you're not understanding it.... Then move on to the specific example of your database.

 

[mrtool]

Ok, well by looking at my database and seeing my problem..you could conclude if it really so or not..

Where can I go post my database..looking at the fact that it's a very big file..where can i go and upload it? speedyshare??