How do I sort a new record automatically in the datasheet?

[andywoj00]

I'm making a simple coin database for personal use. I currently have 2,000+ records in the database. When I add a new record via a master input form I have, and save the record using a Save button, the record saves. However, if you go into the datasheet view for the database, the record is at the bottom of the file.



I'd like to be able to have the new records added sorted. When I used this data in Excel, I'd sort on two fields...Coin Type and Year/Mint. This would help to keep things in order for reports and such.

Am I able to do this as well in Access automatically when adding a new record, or have the sort happen each time the database is opened?

I'm not a code expert, so please be gentle and speak in "see spot run" language. Thanks in advance.

[RuralGuy]

Create a Query of this table and sort it any way you want.

[andywoj00]

Create a Query of this table and sort it any way you want.

Yep, I did that. However, the main database does not keep this sort. Also, if I'm in my master input form for adding coins, the new records that are entered are always at the bottom (last record). I don't like this. I want the records to stay sorted in the main DB table as well, all the time.

Then, If I filter for a particular coin type, they're in order by date and mint mark. Is this action possible?

[RuralGuy]

The Data Handling engine actually pretty much treats all the records of a table as a bucket of records. Depending on how you define your query controls how those records are presented. I suppose if you absolutely need the table in some sort of order (though I can't think of a reason for this) you can set up indexes on the fields you want and then do a Compact and Repair. I believe that will hold but Queries *always* hold their order assuming they have been given one. Queries take up little to no room in your database as they are simply a string of SQL characters and a plan.

[andywoj00]

The Data Handling engine actually pretty much treats all the records of a table as a bucket of records. Depending on how you define your query controls how those records are presented. I suppose if you absolutely need the table in some sort of order (though I can't think of a reason for this) you can set up indexes on the fields you want and then do a Compact and Repair. I believe that will hold but Queries *always* hold their order assuming they have been given one. Queries take up little to no room in your database as they are simply a string of SQL characters and a plan.

Thanks. Appreciate the input. I guess I'm so used to using Excel for my coins that seeing that constant sort of the records is the norm for me. I understand what you're saying about queries and I'm using separate queries for each coin type to gen reports, with the sort holding as you mentioned above.

I may experiment with the indexes you mentioned to see what they do. I'm just getting back into using Access and have forgotten alot of what I used in decades past.

[RuralGuy]

I don't use the Datasheet View but I believe the header allows you to sort the column if you want to. As for separate queries for each coin, I would think one sort would serve all of the coins and just filter for the coin you want.

[andywoj00]

I don't use the Datasheet View but I believe the header allows you to sort the column if you want to. As for separate queries for each coin, I would think one sort would serve all of the coins and just filter for the coin you want.

If you want to generate separate reports for each type of coin, don't you need to have separate queries to pull/sort the info so that it can be put into the report properly? I assume the filter command you're referring to is if you are in the Datasheet view of the DB and want to look at a "list" of a particular coin, correct?

EDIT: Nevermind...just figured out a different way using your filter suggestion to create the report W/O using a query...Thank you!!

[ItsMe]

You should try to think of your tables as confetti. Imagine each table is represented by a different color of confetti. Now, imagine each piece of confetti represents a single field (imagine a cell in Excel) within a specific table. Now take all of your confetti and throw it into a bucket. Think of the bucket as your database.

Individually, the confetti does not mean anything. Collectively, from the perspective of the bucket, your confetti does not mean anything.

If you want to ask a question of your bucket like how many 1905 minted coins are there, you will want to query your bucket. Queries are what you use to retrieve data from your database.

ERD
https://www.youtube.com/watch?v=-fQ-bRllhXc
Keys
https://www.youtube.com/watch?v=_aN-8kszIdA

[andywoj00]

ItsMe:

Cool analogy....confetti in a bucket I can understand.

I'm actually now gen'ing reports using a filter within the report data tab that will only give me the specific coin type I want. Save me from doing a query for each. Using this program is a bit frustrating, but a great learning experience. Relearning the basics at this point and tapping the expertise here or Google when I get stuck.

[ItsMe]

You will want to use Reports and Form to interact with your data. In other words, your Users will have access to the Form and Report objects within your application. Form's and Reports will serve as a user graphical interface.

Form's and Reports have many Properties. One property that both of these objects have is the Filter property. You can assign criteria (a value) to the Filter property in order to limit the data a User will interact with. So the Report can grab all of the confetti that is of a specific color and then, when you apply the Filter property, only certain pieces of confetti will be displayed to the user.

Alternatively, a Query Object can be created saved and named. The query will have the same criteria defined in the Filter property. Then, you can bind your Report to the Query Object, where previously, your report was bound to a specific table.