Hi
How can you set a primary key number (autonumber) to start from a particular number - eg "10000"
I have invoices issued from an old accounts package - and am creating a new database to replace it, but do not want to duplicate old invoice numbers. I seem to remember in the past a way of setting this but cant remember - any help please ?
Thanks
I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.
The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)
We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.
I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.
The check number.
I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.
By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.
I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.
I am trying to avoid the creation of a lookup table where I know I should be able to calculate this. I need to determine the start and end date of a week number. I did find this post from 2002: http://www.access-programmers.co.uk/forums/showthread.php?t=33116&highlight=start+end+dates+week but it doesn't seem to work.
What I am trying to do is show all projects that were opened at the start of a week number. For example, week 2 is from 1/7/2008 and ends on 1/13/2008 (provided that your week starts in the week that 1/1 occurs and that your week starts on Monday). I need this start date so I can show all projects that started on or before 1/7/2008 and were in an open status.
If I can't calculate this then I will have to create a table and relate the query to it.
|Week Number | Start Date | End Date |
I have read and tried though practice to change my Auto Number Field to a start number I want but for some reason fail each time. Help Someone:D
View 4 Replies View RelatedI create autonumber as primary key in a table, then i tried to enter a record in that table. Then i deleted it, but when i enter a new record, auto number started with the next number. Ex: i enter a record and autonumber for it is 1. I deleted this record and entered a new record, but i found autonumber is 2. I want the number is 1,
Why is it happen? any solution ? help me pls...
Thanks
I am working in ms access database form. I have a form with detail section. In detail section i would like to make sr.no field auto increase each time new quotation is created. Using AutoNumber didn't work because because it doesn't start the serial number from 1 for each new quotation.
Snap shot is also attached.
I am calculating number of weeks between start and finish date. Some of the users have questioned the validity of calculation as they are not too sure if this is being calculated as Monday to Friday week or does it work by calculating 7 days. If the latter is true, then I should see a decimal value. I am using date diff formula.
View 12 Replies View RelatedI have new database in this database i make primary key to field (ID NUMBER)
I enter data in this database just copy and past
If I enter any ID Number which is already in database , this database is stock
How I avoid this problem and how I let this database to accept other ID Number which is not in database and ignore other?
Hi there.
I am creating a database and i have the layout I want. although I am having problems as i am using autonumber as the primary key in each of the tables. Is there are way to make this work as the data to be entered into this database does not have a natural key until they are 4 months into the process.
i have attached my relationships diagram.
if it is not a good idea to use an auto number as the primary key does anyone have any suggestions as to what i should use?
Hello ALL,
I want to know how to reset (to start again from 1) my primary key field. As an example i have the "Customers" table with the field "customerID" as PK now i entered many values (20) then i deleted them but i want to start again and the "customerID" value starts with 21 i understand that but i want a fresh DB (i was testing things) and i want to start from 1 can i? how? i even deleted the relation between this table and the other table but no success!
Some help please?
THX in advance
Hello,
I am a beginner and know a little about access. I am trying to create a customized Primary Key with an auto number. I want it to look like this: VP001 ,next record, VP002 and so forth. When I enter a new record and want it to do this automatically. It could be VP1, then VP2, I don't need the extra zeros but it would be nice. I don't know how to do this. If someone could please help walk me through this. Remember I am a beginner with databases and need help.
I would appreciate it
Thanks
Biz
Hello ALL,
I want to know how to reset (to start again from 1) my primary key field. As an example i have the "Customers" table with the field "customerID" as PK now i entered many values (20) then i deleted them but i want to start again and the "customerID" value starts with 21 i understand that but i want a fresh DB (i was testing things) and i want to start from 1 can i? how? i even deleted the relation between this table and the other table but no success!
Some help please?
THX in advance
Can I change the primary key currently set to my products description to an auto number? I'm trying to make things right, also does the foreign key get an auto number?
View 2 Replies View RelatedHi
How can I make the primary key (participant #) in my table equal the record number on the Form? In other words, when I open the form, I need two things to always happen:
1. the primary key must equal the record number (which is displayed on the form)&
2. when I click "Add New Record" button, I want a new customer # automatically generated and in the correct order.
Is this possible? I've been struggling...
When I first was making my database I used all auto numbers for PK's. But then I read an article or two saying that you really don't need to do that if you don't have to (actually, the article said "don't use autonumber if you want anyone to understand your database). I have a few tables (most employee experience type tables) where I just use the word as the PK and there is no auto number in the table at all.I continue to build my database?
View 14 Replies View RelatedIs there a way I can format AutoNumber for a primary key, for example, have the first ID start as C001 then for the next entry Access will automatically go to C002, and so on and so forth?
View 14 Replies View RelatedAssuming the following table:
Album ID AlbumTitle Song Rating
New
When I enter data, Album ID increases every time I jump to the next field. For example, when I enter: AlbumTitle, Song, Rating, Album ID is already at 4 and this is just the first record.
I have created a very simple Access database with a CompaniesTbl, ContactsTbl and CallsTbl. The database is used to record telesales contact with customers. The tables are set up like this:
The CompaniesTbl has an AutoNumber Primary Key field called CompanyID.
The ContactsTbl has an AutoNumber Primary Key field called ContactID and a foreign key called CompanyID.
The CallsTbl has an AutoNumber Primary Key field called CallID and foreign key called ContactID.
The relationships are set up in the relationships window and referential integrity imposed.
Twice now, while editing a contact record, an error has occured. Task Manager has had to be used to get out of it. The error is either a 'run-time error with a message that does not mean anything' or it says 'unrecognized database format' (!?).
If you open the database again the ContactID field in the Contacts Table is no longer a Primary Key field. If you look in the relationships window - the relationship between the Companies and Contacts tables no longer exists.
Any ideas much appreciated.
This is my first project using Access so take it easy on me. I'm trying to convert the Excel application to Access since I think Access is more flexible/powerful. I'm not sure if this requires programming (VBA-over my head) or just need some complex formulas to get the mission accomplished.
The objective is to be able to copy and paste data from a customized form directly to Access. These data will automatically be added into a summary sheet based on volume and amount.
Thank you in advance.
AccessNewby
Im looking at creating a database which includes some sort of diary system but I havent got a clue where to start, how to structure the tables, etc.
I am new to our company so I am looking to streamline some of their processes. Basically there are 4 people in our department and at the moment we use Excel as a mini diary system for our work on a day to day basis (our company uses Lotus Notes not Outlook and the diary system is a load of cr*p, plus also I want to integrate this into a database I have already written for our developers). Going down the page we have a section for each week which contains 4 rows (1 for each person). There is then 5 columns in each section which represents Monday to Friday. Our office administrator then goes in each month and updates the dates manually for the entire month.
Do I need to have a row in my database for every day of the year or is it done in weeks? I did a search for Access diary threads but there doesnt seem to be much info and the example one on iSam seems to have been taken off :(
Hello all,
I am new to Access and I could really use some help to point me in the right direction. I want to organize my tables properly from the start so I do not run into problems down the road. If anyone can give me some advice on topics to read it would be a big help. Here is what I am doing so far.
My database must record warranty claims. A customer could have multiple warranty claims with multiple parts that failed. Each part can be linked to multiple failures but in a claim one part and one failure must be selected.
Example: A motor can fail in many ways but I need to know how it failed for this customer.
I would like to be able cascade the combo boxes so when a part is selected the failures related to that part are updated.
TblClients
ClientID AutoNum PK (1-many with tblMain)
CustomerName text
Address text
(rest of the customer data fields)
TblParts
PartID AutoNum Pk (1-Many with tblMain)
Part text
PartDescription text
TblFailure
FailureID AutoNum Pk (1-many with tblmain)
Failure text
FailureDescription text
TblMain
ClientID
PartID
FailureID
I guess my question is when I make a form based off TblMain, do I need a partfailure table to let me cascade my combo boxes?
Thanks for any help,
Mark
Is there a way to set where the different forms will start, like location wise on the screen, when they are loaded?
View 1 Replies View RelatedI've downloaded and installed the new SQL server 2005 express edition CTD (June ed.) along with the Express Manager.
How do i get an access database into SQL server?
Now that it's installed, what next? When i try to upsize, it asks me to choose a server, and gives 3 options, and i'm not sure where they come from. Where does this list come from? are they previous installations of the MSDE on my computer? are they servers that ar available over our LAN?
i read that the default name if an instance of SQL server express is SQLEXPRESS but that's not on the list...
do i need to create a new instance of SQL server on my computer to house the database?
I really just want to try it, to see if it's something we should do, but i can't get it to do anything at all. i don't know where to start...
I've done some searches, but nothing tells me where to start. I'm getting really frustrated with this....
Please help me...
Good day all. A bit of back ground, I've built a data base (with much help from the folks here) to track the training of my people. Someone way higher then me has decided to completely revamp our field training. There are now two "tiers" based on rank and training level. My first thought is to make two seperate tables for the information in each tier. What I would like to do is make the training specific to each tier show up on the form that I use to track this training based upon the persons rank. Ie an Airman would only show the tier one tasks and a Master Sergeant would show the tier two tasks. I'm thinking this could be done with a quiery but have no idea how to go about it. Any input or suggestions are welcome. Thanks in advance, Tim
View 6 Replies View RelatedHi,
I unticked all the boxes in the Tools > start up window. I closed my database then reopened it. Now, I can't put these settings back again as I can't see the Tools tab.
Any help will be very much appreciated.
B
I have created one access application which will reside on server and many users will access it from there local PC. But whenever we start the application on local PC, it displays worning message that "Code is unsafe , it may harm your PC do you still want to run the code?" and another message box asking same question.
CAn we supress this worning? CAn we design the application in such a way that it will not ask such aquestions and will open application immidiatly when we double click the file.
Please reply ASAP. It's very much urgent...