Automating id numbers after having created them manually so far (2 Viewers)

[dualvba]

Hi

I've got a database, that I'm trying to update for my sister in law.

I've got 2 tables, Items and Customers.

As I've been working with the id's they have used so far, these aren't currently automated.

for customers, they use their number in the auctions, to place bids etc, so I can't change them.

But once I hand over the database, I want to automate the generate of Customer ID numbers from that point on, but not override the history.

I'm not sure how to do this, I hope it is possible?

With the items, what I have done, as an item can be relisted up to 3 times, I created a LogID auto number which is also my key number.

Before I hand it over, I need to do a big update there with them, to pull in the latest customer and item information.

But after that, I'd like the new items added, to be given a new automated number, but the logid still needs to be generated automatically as well, as the login allows no duplicates, whereas the itemID does allow duplicates.

Its the item ID that is used to generate an invoice and show how many times its been listed to pick up correct number of fee charges.

Customers can be both buyers and sellers, and I had to allocate 2 different fields, and the buyerID just replicates the sellerId number. So the Seller number is the main number on the table, I didn't want 2 tables of buyers and sellers separately, its one table.

I hope that makes sense?

Any help or pointers be so greatefully received, thank you.

Clair

 

[HandSam]

Dear Clair, I am not really sure that I got your question right, but I am getting that following that the ID field is an autonumber, you want to rearrange the numbering without overiding the database. If this is what you mean, do this:
Go to the desired report and switch to design view, change the property of the ID field to invisible it turn visible property to false; the introduce a new text box at the space where the hidden ID field is; go to the properties of the new textbox, in the data tab, change the control source to: "=1"; then set 'running sum' property to "over all".

If this is not what you were asking, I am sorry for not getting your question; tis however quite sad that I need to go sleep now, its 2.30 in the morning here, so I can only get your response in the morning, like 4 or 5 hrs from now.

 

[Pat Hartman]

Your items table needs to be two tables. One to define the order and the second to define the items. ItemID becomes OrderID or InvoiceID and becomes unique. The Items table can continue with the autonumber you are using to make the rows unique. The existing ItemID becomes the foreign key to the Order/Invoice table.

If the existing ItemIDs are numeric, you can create the new Order/Invoice table to have an autonumber PK and when you append the summarized data that will become the Order/Invoice, you can append the existing ItemID as the autonumber field. An append query is the only tool available for populating autonumbers with existing values.

 

[dualvba]

Hi

Thanks Pat and HandSam.

My sister in law's current database, isn't an access database, we don't think, and its so tied up in authorisations, we've tried and spent many days trying to get into the tables, and been unsuccessful.

All we could pull off was the items table, which has the Seller ID and name and then the number pulled in for Buyer ID., as well as item description, date first listed, reservice price, and hammer price if its been sold.

So, I've not at present, made my customer ID's field automated, but it is the key number, as I can't change their numbering system, as all the buyers have a little laminated plaque with their ID numbers on it, for using during the auctions, etc.

With the items, I've also kept the item ID number, as well as a log ID.

Because, I want them to be able to relist the item, rather than just overwriting the data manually each week they relist it.

So at the end of the 3 weeks (for example), they can see 3 entries, with 3 sets of listing fee's for that one item, but with 3 different log ids

I hope that makes sense?

But, I'm going to have to go and do a final update with their data on the current database, as the last update I got when I visitem them last, as the table as at 24th March 2012.

Then, I'd like to someone start the Customer ID's and Item ID's to become automated.

Can I automate them? Without overriding the previous id's within the database?

Sorry, I hope thats clearer?

Thanks guys for your responses so far.

Should I split out buyers and sellers? Should I split items for sale from items purchased? All unsold items either get relisted for the next week (max 3 times) and then they get returned?

Thanks so much
Clair

 

[RainLover]

My sister in law's current database, isn't an access database, we don't think,

You need to explain this a little more.

What is the extension on the file name.

Is it Something.mde

 

[dualvba]

Hi all they have on their system is an EXE file. They want reports, and all sorts that weren't built into the original database, I thought it better, and easier to manage to start again. I've had alot of help from guys on here to, trying to get to the bottom of it, and we just can't get into the original main database, they guy has really made it hard to access, as he wouldn't have wanted anyone esle to help them, just him (and he was expensive, but clearly a very clever chap).

So I have 2 main tables, customers (who can be sellers, and buyers).

and

I have a large item table, that shows all items, the date entered, the latest auction date (they have been manually over writing each item, each week, to relist it), seller id and name, buyer Id (which is the same as seller, but its own named field), hammer price item description, item id, logID (which I added), reserve price.

I can't over ride the exisiting customers and their numbers. But want to be able to make the customer numbers automated, once I am in a position to hand the database to my sister in law, so they don't need to manually enter a new number each time they add a new customer.

Same with the item id numbers, I've used the ones of the original tables I was given, and these need to be automated as well, but not overwriting the history.

They at present, manually over right all items that are to be relisted, rather than create new lines in the table, so it is very hard for them to accurately pull invoices off, with all correct listing fee's.

I hope that makes more sense? I have other forum threads open asking for help regarding setting up invoices and automating relisting items.

I've taken on a huge task, I know, but really want to do my best for them.

Thank you again
Clair

 

[Pat Hartman]

If your sister-in-law paid for custom software, she is entitled to the unsecured source code and data files unless her agreement with the developer said that he owned it. Even if he owns the software, she owns the data and he should give her access to the data file. Check with a lawyer or just call the developer and tell him you want it.

 

[RainLover]

By the looks of it this is not an Access Database.