Total Noobie here... question about auto numbering

[JavaBeans]

Good Morning all

I have an access database that's published to sharePoint services. The database contains one table. We used Microsoft's 'Assets Database' Template to initially set it up. I've changed quite a bit around in the template to make it fit our needs, and things seem to be coming along just fine for the most part. But like my post states.... TOTAL NOOBIE to Access.

My question is:
We used auto numbering for each record, the auto number is the first field in the database ( I believe this is my primary key as well). when a user deletes a record - say record 4 for example - the database does not renumber the records it just shows the records in order as 1, 2, 3, .. , 5, 6, etc... skipping number 4. So this puts my users in a position where they have no idea how many actual records are in the database. Is there a way to have access re-number the records? I assumed when I setup auto numbering for column 1 that it would do this.

Thanks!

[June7]

Renumbering primary key not a good idea if there are related child records. Why do users need to know how many records? Why do users delete records? Record deletion should be rare and have strong justification. Can use Count(*) function as textbox ControlSource to return record count.

[JavaBeans]

They've deleted records that are duplicates as they come across them - since this database is new there have been several dups that users have came across. In the future deleting records shouldn't be an issue, but as of now, like i said, they are mainly deleting duplicates.

They need to know how many records are in each state.. we have two tabs. One is records that are currently being worked the other tab is records that have been completed. I will soon be adding another tab for records that are in a different status. So, the users need to report to their manager how many records that need to be worked and how many records have been completed. And when I add the other tab they will need to report how many records are in that status.

As of now, I have it where a user ticks a check box in a column labeled 'done'. When that box is checked it moves it to the completed tab. So when this happens it also messes up the numbering scheme.

I'll look into the Count function for this. Can you steer me in the right was as to how to go about using the count function.

Also, is it a good idea to have one table for what I explained above - the three tabs for the different record status'. or would best practice be to have a table for each status.... like when they click the checkbox in the done column it moves it to a different table rather than using a function to display the different data for each tab.

I appreciate your help!

[orange]

You could have a table for Status.

Id autonumber
StatusName text

Then in your other Table where the records are, you could add a field, "recStatus" that would contain the Id of the appropriate status from the Status table.

To determine How Many records were in each Status you'd run a query

Select refStatus, Count(*) from myTable
Group By RefStatus

or
Select StatusName, Count(*)
From Status, MyTable
Where Statusid = MyTable.refstatus
group by StatusName

see http://www.techonthenet.com/sql/group_by.php


As for the autonumber, if the number involved has meaning for you, you have probably misused the autonumber.
Autonumbers are considered for system use. They are meant to be unique, not necessarily sequential. They are used primarily to uniquely identify each record in a table.

[JavaBeans]

I added the new table 'Status' and created three records for the three status' I need... working, completed, and rescinded.
When I to to create the record recStatus in my data table, I'm not sure what type of column I need to create.

[orange]

recStatus would be number datatype integer

You will join the Status table and the data table on

recStatus = Status.Id

something like

Select StatusName, Count(MyTable.*)
From Status INNER JOIN MyTable
ON Statusid = MyTable.refstatus
group by StatusName

[JavaBeans]

What your saying makes sense, but I'm not sure how to apply this to my DB. This DB was initially setup.... well partially setup... by my IT director using the Microsoft 'Assets Web Database' Template.

The way information is pulled from the data table to the form MAIN, flows like this:

Main (form) Recon Tab -> AssetsCurrent (form) -> AssetsDS (form) -> AssetsOpen (Query)

Main (form) Recorded Tab -> AssetsRetired (form) -> AssetsRetiredDS (form) -> AssetsRetired (Query)



When I am in layout view on the form I right click on a tab and choose Properties and can see under the Data tab in the pop-up that the 'Navigation Target' points to each of these forms.


I'm wondering if I should take the tabs on the form MAIN and point them directly to the queries. There are three queries, AssetsExtended, AssetsOpen, and AssetsRetired.
The Reconveyance tab on my form MAIN shows the data from the AssetsOpen Query and the Recorded tab from the form MAIN shows data from the AssetsRetired Query. But the flow of how this data gets from the query to the actual tabs is shown above.

The query AssetsOpen simply shows data that does not have the 'Done' Checkbox ticked. And the AssetsRetired query shows data that does have the 'Done' checkbox ticked.

Sorry, if i'm being confusing, this is all VERY new to me.

I attached the database if you want to have a quick look to see what I'm working with.

Thanks, I appreciate you help!

[orange]

I have acc2003 and can not open an accdb.

[JavaBeans]

Damn look at that!

[JavaBeans]

Thanks for your help. I think I am starting to get a pretty good grasp on this. I very much appreciate your help and letting me bounce idea's off of ya. The code for getting a count of the number of records and the idea about creating a new table has helped tremendously.

I'm redesigning the db now, if you don't mind I may bounce an idea or two off of you or ask a few questions while I'm doing this.

Thanks!

-Brad

[orange]

We are all here to help.