I am trying to create an automatic unique 'number' (actually text) in a form. Here is an example of the format...
1456.R1
1456 is the project number
R stands for revision
1 is the first revision
So, in this database there could be 1456.R2 etc. but there could also be other project numbers, say 2323.R1, 2323. Looking for expression I need to enter to have Access look up the last revision for a specific project and then add 1 to it?
Hello i want to create a tracking number based on information entered into a form.
So i have my main table called "MainData" and this is in the backend and linked through to the front end where my form is.
I then have a query which is called "calllog" which has a field called "Call Number" inwhich i placed the following
=Year([Date]) & Left([CustomerName],3) & Right([Date Created],2)
This then gives me a unique number. Once i have entered a job in it will generate a number. Now this part works fine but it does not update the Maindata. How can i get the this to transfer the data to the maindata table???
I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.
The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.
This is kind of like library catalog numbers. Not sure how to do this.
So I have this field in a form/table called "CustomerID". This is to be an ID number for our customers that includes the first four letters of their last name (LastName field) followed by four digits.
This ID is created when a button is pressed in the form. After that, code needs to grab the first four letters of the last name (LastName field in the form and table) and then go to the Contacts table and see if there is already a CustomerID that starts with those four letters. If there is not, then the CustomerID will be those four letters followed by "0001". If there is, it needs to be those four letter followed by the next number sequence that still does not exist for those four letters.
For example:
First four letters of last name = zabo
Check to see if those four letters exist in CustomerID returns negative then
CustomerID assigned = zabo0001
Another example:
First four letters of last name = zabo
Check to see if those four letters exist in CustomerID returns zabo0001 and zabo002 then
CustomerID assigned = zabo0003
I would like to know how to have conditional formula (code) for my monitoring. I have the following fields for form [1]:
Date today
Date Encountered
Date Closed
No. of Days
I need an automatic computation of the No. of days until it is closed. Below is the computation I want to have:
If it is not yet closed: [Date today] minus [Date Encountered]
If already closed: [Date Closed] minus [Date Encountered]
I tried using a code builder:
If Forms![1]![Date Closed].Value<>""Then
Forms![1]![No. of Days]="[Date today]-[Date Encountered]"
Else
Forms![1]![No. of Days]="[Date Closed]-[Date Encountered]"
End if
but there is error.
Need to generate a unique reference number each record wise while data entering by a form. How to do this by using a command button ? After entering all fields related to the record, when click on the command button, unique reference number for that record need to be generated and save with all other data of the records. in a later event, need to recall the record by using this reference number and also need to make relation ship with some other table by using the reference number.
View 6 Replies View RelatedI am attempting to creae a hyperlink to set into a table that is generated automatically by other information curently residing on the table, and with the ability to change the disc location.
The table I have created is an application that will name reports that I have in .PDF format. I put in the report title, date, inspectors, etc. and it gives me a unique name for the document. I then go to my document and name it what the application gives me. Now, we would like to have a link to that file that resides on the table, so if we look it up in the future we don't have to search any further, we can just click right there and go strait to it.
I know how to insert a hyperlink to get to the file, assuming it stays on the same directory. Our directories change fairly often (can go from C:/My Docs/File to E:/My Docs/File in a matter of a week), so I would like to know if there is a way to create a partial hyperlink that I can change the begining to whenever need be.
Any ideas or suggestions are greatly apericiated.
Thanks guys!
Hello,
I am basically doing FORM to automatically figure out my stylists commission pay, based on 5 levels of sales and 3 different pay levels, that I will cut and paste in a table from my POS system.
3 Total Tables.
tbl_Employees, with the headers ID, Name, Payroll ID, Comm_Level.
tbl-Current Year with the headers ID, Payroll ID, Name, Service Sales.
tbl_Comm with the headers ID, CommLow, CommHigh, Level1, Level2, Level3
In a query, Qry_Years, I have Payroll ID, linked between the tbl_Employees and tbl-Current Year.
I am close in a FORM to getting this to work, unless DLookup is the wrong why to go about this.
This is not correct, but gives you an idea of what I am trying to do. In a FORM text box (called framePayrollIDLookup) I have
=DLookUp("Level1","tbl_Comm",[tbl - Current Year.Service Sales] & " between [CommLow] and [CommHigh]").
This pulls up the correct pay percent for level1 employees, but i need to change out Level1 to something variable.
This also doesn't work, but gives you an idea of my though process.
=DLookUp(" " = framePayrollIDLookup,"tbl_Comm",[tbl - Current Year.Service Sales] & " between [CommLow] and [CommHigh]"), but this doesn't work.
I might be way off base on how to do this, but I am trying. Thanks for any help.
Chris
I want to create a form that incorporates a number of different tables & tabs. When I switch tabs (which represent the information on the different tables) I need it to be consistent with the main table and all the information that follows it from the different tables.I've been told the best way to do that is with a parent form but I haven't been able to find an EASY step-by-step instruction on how to do that.
View 2 Replies View RelatedHi,
Im putting together a membership data base. the data base has an already existing client base which all have membership numbers in the form of first letter of there sir name, last to numbers of the year and a 4 digit number. So for example Joe Bloggs membership number would be B06001 his sister Betty Bloggs would be B06002 and the guy next door John Doh would be D06001.
What I would like to do it automate this system so everytime I make a new record when I input the sir name into the 'lastname' field on the form it checks the last Membership number and makes a new one. I wouild also like it to change the 06 to 07 when the year changes.
Ive done searches for things like this with Dmax etc but i really could use help with the code as I have no idea how to code this type of thing?
Any ideas on this oue would be great!
Cheers
Phill
Hi All.
Is there a way to index and create a unique identifier using a combination of fields (eg. User# + Provider + Date)?
Thanks.
I have a form with a combobox where I select the Carrier witch then populates a multiselect listbox with all the record from that carrier in the specified date. When I select them and click save I want all the record ID(ConID) saved under the same ID(manifest ID) ie,
1 223
1 225
2 344
2 4444
Ect
It saves it in a table called Manifest With the fields Manifest ID and CONID which is a lookup field to a different table. Here is my current code for the on click command
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Manifest", dbOpenDynaset)
[Code] ....
Now I've done some searching and I thin dmax is what I want to use.. But I am not sure how to make it work...
I'm trying to create a database for a construction company, as an experiment more than anything, and my problem is this:
I need to create a set of identical records in the table "buildings" based on the information entered into a text box by a user on the form for registering a new project. I want each record to be identical except for the "plot number" field, which I want to start at one and increase to the number of buildings defined in the text box.
"Projects" is one table and "Plots" is in the other.
I have a table as followed;
EmployeeID: PK
FirstName
MInitial
LastName
Company:FK
Department:FK
What I want to do is create a new field that automatically populates based on what is entered into the above fields.
Essentially creating a single field with unique data generated by multiple fields.
For Example if I entered the following informaiton:
EmployeeID: 1 (AutoNumber)
FirstName: John
MInitial: P
LastName: Doe
Company: FederalGov
Department:Test
The new field can be generated as followed: JohnPDoeFederalgovTest
What i plan on doing with this is making this "generated" field an index so no new duplicated records can be added.
Hi There,
I am setting up a basic Data Licence database. For each new record I enter I want a unique number to appear at the top of my form that can not be changed. I would like the number to be the date it is entered plus the auto number so, YYMMDD-(auto number) eg 071001-1. I'm sure it's something simple but I am only new to access.
Also while I'm here, on my form I have a Product field, how can I make it so I can enter in more then one product?
I would really appreciate any help!
Thank you,
Dane
How i would best combine values in a table to produce a 'primary key id number.'
For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233
I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elaborate some more.
if I got a 4 x 4 matrix table - 4 rows and 4 columns - MS Access 2007/2010 the values should exist as below with no repetition of any number in any of the cells.
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16
a number should not repeat in any of the cells.I set a primary key on cloumn 1 and defined unique on all the fileds but that doesn't work since 1 columns does not know what the other cell contains and no relationship exists.
I am trying to take a table that looks like this:
Customer Name
Fruit
Customer A
Apples
Customer A
Bananas
Customer B
Pears
Customer B
Apples
Customer C
Pears
And create a table that looks like this:
Customer Name
Apples
Bananas
Pears
Customer A
Apples
Bananas
Customer B
Apples
Pears
Customer C
Pears
I think this is relatively simple but don't know the syntax to create this table.
Ok,
Bacically I have 2 different lists (well there will be 7 in total):
tblValves
ValveID
KKS_no
Description
List - specifies thats its in the valves list
TOP_Code
tblSpools (pipe sections)
SpoolID
SpoolTagNo
Description
List - specifies thats its in the spools list
TOP_Code
The reason that I have kept these two in different tables rather than combining them is because the spools table will relate to a few tables that will have nothing to do with valves. For instance the spools table will relate to a table called tblPipeSupports.
The thing is this: I would like if there was a unique number across both tables. Collectively the items in coth tables are called components. Some of the components from either table will have drawing numbers for instance so I would like if I could have a table:
tblDrawings
DrawingID
DrawingTitle
ComponentID (FK to valveID or spoolID)
However this won't work because valveID or SpoolID could have the same numbers. I could use a composit key like SpoolID + List but I think I'm probally making it more complicated than necessary. Also I'll have several tables like the drawing table e.g. some of the components will belong in the testPack table... And I'l have to repeat that List field in each..
So what do I do?
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.
Table A
Field X Field Y
1 a
1 b
1 c
1 d
2 a
2 b
2 c
Is there a way of merging 2 fields together to create an additional field
my database consists of 4 main tables (in order of relationships)
*HeadOfficeDetails
*SiteDetails
*ContainersOnSite *Contracts2015-2016
For example;
Account Reference: TEST
Site Number: 001
and the field i would like to have;
Site Reference: TEST/001
I would also like that when i add a new site to that account i will have TEST/002....
I am selling some products, namely A to M. I record the orders of costumers in a table, named "orders". For example, if costumer "Jack" has ordered as the following:
Code:
ID costumer product quantity unitprice orderdate
12 jack A 4 10 25/11/2014
13 jack B 5 5 25/11/2014
14 jack C 1 4 25/11/2014
15 jack F 3 2 25/11/2014
16 Jack G 4 8 25/11/2014
17 Jack H 1 7 25/11/2014
Then I make a report based on the above table and issue that as the invoice to the costumer, who is jack here.
How can I make a unique invoice number, which automatically increases sequentially for each invoice? Do I need to add a new column to the table? I also need to be able to look for a specific invoice based on the number, or date.
My table : Id Start End
1 2013.11.20 2014.01.10 2 2014.01.17 2014.01.30 3 2014.01.06 2014.01.27 4 2014.02.04 2014.02.23 5 2014.02.07 2014.02.17 6 2014.02.21 2014.03.08
How to calculate total number of unique days? Date ranges are overlapped.
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 )
Example 1:
2011-1
.......
2011-3893 etc.
Currently I have an Access form which produces a new unique number to identify each new record created. To do this I use the unique ID autonumber from a table to identify the new records. I would like to change from this simple number to the the above format per example 1. The four digits to the left of the hyphen would always be the current year and digits to the right of the hyphen would be the unique auto incrementing numbers such as from my table. I need the year to auto increment by 1 each September 30th (new business year) and I need the numbers to the right to auto reset to 1 to start uniquely identifying records again for the new incremented year. As each record is closed I need the number to be written as a single entity in the new format to my database.
Example 2: After September 30th.
2012-1
.......
2012-447 etc.