Creating ID Number In Access
Mar 1, 2012
at the moment we have been given this problem where we have to incoporate a Dmax function into the following code
IDnumber = Left(Firstname, 3) + Left(Lastname, 2)
from this code the ID number creates something that looks like this
gembl
however i need a four digit number after this ID, for every different ID i need the numbers to increase by one the following code was given to us to do in Got Focus but this did not work
IDnumber = Left(Firstname, 3) + Left(Lastname, 2) + Trim(Str(DMax("right(IDnumber,4)","Customer tbl")+1))
View Replies
ADVERTISEMENT
Mar 20, 2012
I have a table called "OrderDetails" with following fields:
Num
OrderID (Primary key)
Product
Quantity
Price
I want to create a data entry subform that can used to enter order details in this table such that, for a given OrderID, the Num field is automatically set to previous number + 1. For example, for OrderID = 12, if there are 4 products that need to be entered, the 4 records should automatically take 1 , 2, 3, 4.
View 6 Replies
View Related
Oct 29, 2013
I am using a template database that I downloaded from the Microsoft Access template website and have been modifying it and adding new forms and tables etc. I have used the forms wizard to create a number of new forms to edit a number of the tables. The forms are "split forms".I can't seem to get many of the forms to stay the size that I set them to! They seem to have a mind of their own and often when I think that I have sized them correctly, I then open the form and they display in full screen mode or larger!
View 8 Replies
View Related
Jul 8, 2006
Quick question, how do I have access generate a random number between 1 and 8? I want it to generate either a 1,2,3,4,5,6,7, or 8. (no zero, no nine)
Expr1: Fix(Rnd(8-1)*10*Rnd()) is what I have been using, but this always generates the number 2? I do want the decimals to be rounded to the nearest integer, thus I have Fix.
If someone could please help me, it would be greatly appreciated! Thanks in advance.
View 3 Replies
View Related
Jul 19, 2007
I'm trying to create an auto generating ID number field that contains a date followed by a sequential number for that date. In example...
YearMonthDay###
070719001
070719002
070719003
tomorrows numbering would be
070720001
070720002 and so on.
Each new day would start a new sequence of 001, 002, 003, etc.
I have searched these forums and haven't really found any answers. Could someone direct me in the correct direction. Also I need this number to be saved in the table and not just displayed or printed.
View 9 Replies
View Related
Oct 19, 2005
Hi there,
I'm making a document control database where the a unique reference number is automatically generated from 2 tables and updates a 3rd table.
xxxx or yyyy/lookup in 2 table/autonumber
I have been trying to do this is a form but because the control sauce is a calculation 'Text1&Text2&Text3' I am unable to update the 3rd table with the combined reference number.
I have limited knowledge particularly of VBA coding
Any help much appreciated
View 1 Replies
View Related
Sep 27, 2004
Hi,
I am v new to Access and am creating my first DB.
I want to create a unique client office code....
For example, for a (law firm) client name Brown Smegmore in Sydney,
I want to create a code like BS_S_1, Brown Smegmore in Melbourne: BS_M_2
etc
I would really really like some simple help
cheer
Deano
View 3 Replies
View Related
Aug 19, 2013
What I have is a small POS system I'm trying to create a unique invoice number for each transaction (multiple records)
I have two tables, one named "CurrentTransactions" and one named "Past Transactions".
I have everything working except the invoice number my system works by adding pre defined records into the table "CurrentTransactions" and once the sale has been finalised it transfers the data to the "PastTransactions" table.
take the last number from the "PastTransactions"."InvoiceNumber" to create an invoice number in the "CurrentTransactions", and I want to be able to do it using an expression in the default value property as opposed to in the form ( I've tried the form way but using Dmax would require me to almost rebuild my entire POS system )
View 5 Replies
View Related
May 6, 2013
I am trying to create an auto-sequential order number
Example : AB000112 - AB is standard prefix, ''0001'' is the first invoice number, ''12'' is the year 2012.
so the next order number should read AB000212 and the next AB000312
Out of this, I have to issue several debit notes relating to the same order number when the payments are due from customers over a period of time, this will be
001AB000112
002AB000112
003AB000112
Can such numbers be the primary key?
View 4 Replies
View Related
Oct 14, 2011
Just fumbling my way around Access 2007, is the only way you can create a drop down list of number by creating a table and inserting a combo box? I am trying to create a list of hours for example:
0.5
1
1.5
2
Etc...
View 1 Replies
View Related
Feb 15, 2013
I have a rental database and I print several contacts for leases etc. what I want to do is have a unique reference number or something inserted to the report every time that it's printed. What I am trying to achieve is to keep track of which tenant corresponds to the report (Lease) printed by using reference number.
There is a seperate form which holds the tenants details and I would like to have a field on that form which would show the same reference number as the report so I can track which report was printed for who.
Whats the best/easiest way to accomplish this?
View 1 Replies
View Related
Mar 31, 2015
I want to create a text box within a form that automatically populates a contact number based on a selection from a combo box, also in the same form.
For example, I have a Bidders Table (tblTenders), this form includes information regarding the Tendor like the company name and a main contact within that company and a phone number for that contact.
I've created a separate table for all the contacts called tblContacts. This table holds all the contact information for each contact. I have a simple form called frmTenders that asks the user to input the Customer (which is the company who are bidding) and the Main Contact, which is a combo box to select the main contact from tblContacts. Below that combo box is a text box called 'Contact Number' - I want this box to display the contact number for the main contact automatically when a main contact is selected from the combo box.
The contact number text box isn't storing that information in any tables etc. It's just for viewing purposes when we need to make a call to that specific tender.
View 5 Replies
View Related
May 31, 2013
I'm creating a database using existing data from an excel file full of contact details. What I need to add is a queue type system where each contact in the database has a "Place in Queue" number which is unique obviously.
Lets say Alan is number 1, Bob is 2 and Chris is 3. They have these corresponding numbers in the queue field for their entries.
Now what I need to have, through use of a form, is a way of changing Chris from number 3 in the queue to number 1 and thus have Alan automatically shift down to number 2 and Bob to 3.
View 4 Replies
View Related
Oct 9, 2006
Hi,
I have created an Access Form wherein i take the user input in various text boxs and text areas.
Now plz help me to find out how to create Microsoft Word Document automatically from the information which the user has submitted.
I have provided a button named submit which when clicked will submit the information into the Access Database and using that data i want to create a Word Document into a particular format.
What is the way in which this could be achieved?
Please help me out to find a solution to this problem.
Thanking you,
with regards,
Kapil
View 12 Replies
View Related
Apr 25, 2006
Hello everyone!
I heard from a number of people that this is the place to come for Microsoft Access related help.
So here is my question and I hope someone can help me understand access a little bit better.
I am trying to create a database for a job I am doing. THere are 700+ convenience stores that we (4 inspectors) inspect every three months.
We have been told to do an ADA compliance survey when we visit these stores.
The ADA checklist that we have is about 78 questions and my manager wants me to create an ADA database for this information.
So I am in need of help on how to create a database that allows me to input the answers of this ADA checklist for each store inspected.
I then would like to be able to pull all that information into a report that I could say of the 700+ stores, 50% passed all questions, 25% passed only questions 1-??, etc. etc.
I can upload the two files: The ADA checklist and our Store list if anyone would like to help me out!
Thanks Much!
View 9 Replies
View Related
May 11, 2006
hello :)
how can i create a view in access? is it possible using any of the wizards or can i even use the sql coding feature??
is it possible to grant views to users???
View 2 Replies
View Related
Apr 2, 2008
Iam new to access and having problems create database tables and their relationships, from the following details without duplication.
Tape Identification Numberà unique tape identity
Date -à the date the backup is done .
Backup type à either Daily or weekly or monthly or Full System or Yearly or Special
Duration à either automatic or Manual
Technician name à Person performing the Backup
Comment à comments about the Tape’s history
Location à tape is either located onsite or offsite.
Number of use à Number of times the tape has been used
Restoration à tapes restoration Details, either yes/no
Requirements
1.perform backup everyday
2.Perform restoration everyday
3.Search for tape from offsite storage
a.Tape should be located offsite
b.If it was used for daily backup, it should be at least 60 days old from current date.
c.If it was used for Weekly backup, it should be at least 6 months old.
d.If it was used for any other backup type, do not use.
** the database should provide history
View 1 Replies
View Related
Nov 4, 2004
I am working with some chemical compounds in a database I am building. I grabbed an html file off the web on a listing of chemical compounds...converted it to ascii and imported it into ACCESS. Rather than write a Perl routine I was wondering if anyone out there had some sql code that might handle it. The first 3 records look like this and are contained in one field. I am trying to break this guy into 4 fields.
0001 Acetaldehyde 75-07-0 AB1925000
0002 Acetic acid 64-19-7 AF1225000
003 Acetic annhydride 108-24-7 AK1925000
The fields would be named in order as:
GUIDE 002
CHEMICAL_NAME Acetic acid
CAS_NO 64-19-7
RTECS_NO AF1225000
The first two fields are left justified but the number fields are rather zigzagged throughout.
Anyone have a cure for this beast?
I thank you in advance.
the raven man.
View 8 Replies
View Related
May 26, 2005
All..
I have an access 2003 application I have front end and back end separately. When I click "Make MDE File" from tools menu, nothing happens.
When checked the Windows Task manager(Windows 2000), CPU usage is 100% and MS ACCESS is not responding. There are no messages.
Anyone experienced this problem ?
Thanks
Bosch
View 3 Replies
View Related
Sep 1, 2005
Hi guys, i need help with a sample database, and wondered if anyone could tell me the entities and how to calculate the prices etc. If anyone could attempt starting the database off for me it would be appreciated too!!
Here is the spec!!!
You are required to produce and document a design that meets the requirements of the McDuffs Burgers scenario:
The corporate office of McDuffs Burgers has asked you to design a database to help track its restaurants and managers. The database is to help the management show the total annual sales of each restaurant and the performance of each manager, as measured by the totals annual sales of all restaurants for that particular manager. Each restaurant is supervised by a single manager, but a manager is also responsible for several restaurants. The company stores typical personnel data (name, salary, and so on.) for each manager as well as basic data for each restaurant such as the telephone and address of each restaurant, its size in square metres, and total annual sales for the last fiscal year. The company would also like objective ways to measure the performance of a manager such as the total revenue for which they are responsible, the average annual revenue per restaurant, the average annual revenue per square foot, etc.
The database should also track the orders that are placed by individual restaurants to the corporate office for various food supplies. Each order is associated with a specific restaurant, and of course, a single restaurant will place multiple orders during the course of a year. The company uses a standard set of product numbers, product descriptions, and associated prices that applies to all restaurants. Each order can specify several products, and a single product may appear in several orders. The database should be capable of computing the total cost for each order.
Deliverables
1. Entity definition for each entity.
2. Entity Relationship Diagram, which must show entities, relationships and membership.
3. Relationship definition for each relationship.
4. Relations (This must include for each relation the primary and foreign keys).
5. Data Dictionary.
Furthermore:
You are required to implement the design (produced in the first part of the assessment - McDuffs Burgers - Database Design), by designing and creating queries, forms, reports and any supporting code. Revisions may be made to the design in the implementation process.
You should note the management of McDuffs Burgers has little experience of database systems and wish to be advised on the information the system can produce.
They require example reports demonstrating the capabilities of the system to:
- Aid the day to day operation of the business.
- Provide appropriate management information.
You must also implement an appropriate user interface to the database easy to use.
View 5 Replies
View Related
Aug 7, 2006
Does anybody know how to schedule in access like sql. Like a "job" in sql server?
View 14 Replies
View Related
Aug 8, 2006
I am trying to make a DAP for data input into a table.
I used the wizard and it has the fields I want, I can scroll through them and view the entries, but they are not editable.
I read about changing the Page DataEntry property to True, but I still cannot select any of the editboxes on the DAP and now the DAP just shows blank non-selectable editboxes.
I decided to create a dummy table to work with to test creating a DAP, my dummy table looks like this:
Group Name Number Text
1 Bill 3 pink
1 Adam 5 green
2 Charlie 7 blue
I ran the wizard on the above table and again I can scroll through but not select any of the fields, and with DataEntry set to True I still cannot select/edit the textboxes.
Is there something else I need to do to be able to make an input form?
Once I get that sorted out I have a followup question: The dummy table above is where the input data goes, but on the input form I would like if possible to get the Group and Name from a different table using drop-lists. The real table has over 5000 names split into groups of about 30-50 so picking a group first would help find a name in the list which will eventually be input to the table above.
Hope that all makes sense, and thanks in advance for any help!
View 1 Replies
View Related
Dec 7, 2007
I have MSAccess 2003 running on WindowsXP. I have multiple users sharing a single database. When more than one user opens the same database, a copy of the database is being created?? I don't know if this is a standard trait of Acccess or something else. Any explinations would be helpfull.
Thanks
Bob
View 2 Replies
View Related
Mar 24, 2008
I converted an MS Access 2000 DB to 2003 with no problems. I am able to open it up in Access 2003 now either by double clicking it or from Acccess - file open. I then created an MDE file in Access 2003, but when I tried to open it up I got a message saying
This database is in an unrecognized format.
The database may have been created with a LATER version of Microsoft Office Access than the one you are using. Upgrade your version of Microsoft Office Access to the current one, then open this database.
I noticed a service pack will get rid of this errror. My question is if I distribute the MDE will the user get the same error if they don't have the service pack installed on their PC? Will my installation of the service pack fix the errror and allow anyone to use the file without getting the message.
Thanks
View 2 Replies
View Related
May 9, 2006
Hi all,
I write a program which is querying some data from a MS-Access database.
The problem here is, that the tablenames contain characters like "/" or "-" and I
do not want to change these names, because I think that the database
structure will be damaged.
To solve this problem I thought of creating a view to the table. I tried creating
this with a SQL statement, but that did not work.
Is there any other possibilty to create a view on a MS-Access table?
Regards,
Stefan
View 2 Replies
View Related
Feb 5, 2008
Hi,
I am trying to create a series of MS access tables from within a SQL 2000 DTS package. The names of the tables reflect the date range of the data within them. Creating the "Create table ddmmyy ...." statement isn't a problem (full code below), but doesn't run as it creates a Jet error - "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. Does anyone know if what I'm trying to do is possible.
Before anyone suggests it, yes I have posted this on SQL server forums.
declare @TheName char (8)
,@Cmd char (500)
select @TheName = (select max(LastRunDate) from NSP_Analysis_Dates)
select @Cmd = 'CREATE TABLE `NSP_Analysis_' + @TheName + '` (`TYPE` VarChar (200) NULL, `VENDOR_NO` VarChar (10) NULL, `VENDOR_NAME` VarChar (50) NULL, `AMOUNT_CLAIMED` Currency NULL, `REGION` VarChar (100) NULL, `CLUSTER` VarChar (100) NULL, `PLANT_CODE` VarChar (15) NULL, `PLANT_NAME` VarChar (50) NULL, `DATE_OF_SERVICE` DateTime NULL, `STATUS` VarChar (200) NULL, `TICKET_NO` VarChar (20) NULL, `NOTES` VarChar (255) NULL, `DATE_ENTERED` DateTime NULL, `AuthorisedByName` VarChar (50) NULL)'
sp_executesql @Cmd
View 1 Replies
View Related
