Access database query

RedBern

Hi, I'm involved with a social enterprise, and we have a stock database which I've imported from excel into access.

Some of the stock has been sold - so the item numbers weren't on the excel sheet. However, a trawl through everything has revealed some stock that was marked as sold but that is still in our possession. I therefore need to add a record to the access database.

Our stock numbering system is simple - it began at no. 1 and goes straight through! However, when I tried to add item 451 to the list - it has to go on the end. Is there a way, in Access, to re-order the list in the correct numberical order?

Thanks

fitshase

I am assuming that you are using the stock number as the primary key in the database (probably defaulted to this if you imported it). If so, unfortunately you can't as there would be another 451 in there (assuming that there is more than 451 stock items on the database).

The only way to do it would be to add another field in the database and have that as the stock number. You would then have to go back through them and manually input the stock number. Make sure the field is set to "no duplicates" to avoid 2 items being given the same stock number.

Then, it would be a simple case of telling Access to sort the view by stock number and not primary key (usually by way of a query).

Lokolo

^^ to go on from fitshase....

What fields have you got for each product? (name, description etc.etc.)

TimothyEBaldwin

RedBern wrote: »

Is there a way, in Access, to re-order the list in the correct numberical order?

They are many ways, in Datasheet or Form view right click on the column and choose sort, or use the sort row in query design view, or use the SQL "ORDER BY" clause.

RedBern

thanks for the replies.

I've ignored the ID key (the first column which is generated by the program) and the second column is the first of my database (if you get me) and that is the stock number which I've added manually.

Following this is type (hat, scarf, necklace etc)
colour
brand
size
price
date sold.

if I do as you say fitshaze, can I then save that as the 'master' table?

TimothyEBaldwin

fitshase wrote: »

You would then have to go back through them and manually input the stock number.

That can be done with a trivial update query. In SQL it would be like:

UPDATE [Stock] SET [New stock number] = [Old stock number];

You can of course use the query design view to create it.

RedBern

TimothyEBaldwin wrote: »

They are many ways, in Datasheet or Form view right click on the column and choose sort, or use the sort row in query design view, or use the SQL "ORDER BY" clause.

that sorted it - thanks.

Second query - is it possible - now that I've started manually putting numbers in - to get it to do it automatically 'from this point on' so to speak? (I left a lot of numbers out of the stocklist - items already sold). I have a separate accessories list - which is numbered 1a, 2a etc. Can I get an automatic numbering that always puts 'a' on the end?
Thanks for your help.

johnmc

Step 1
register with www.utteraccess.com

Step 2
The default value can be set in a Form's "on load" to be the key value plus the letter.