Forms :: Create A Unique Customer ID

Apr 4, 2013

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

View Replies


ADVERTISEMENT

Create New Table With Unique Customer Rows

Mar 6, 2014

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.

View 2 Replies View Related

Add Customer Name Into Table Each Time Unique Customer Added To SaleTable

Dec 11, 2013

I have a Table Sales - that lists customer and order information

Most of the customers are new - is there a way to populate Customer Name Table with a new entry each time a new customer is input into the Sales Tale

(Without having to add the custome to Customer Name Table - then going to Sales Order)
OR
Failing that - is there a way to use a list box that points to Customer Name Table (and if it doesn't find the one you want, will allow you toadd a new record) In the SAME form as you use to add to Sales Table.

So:
Date (textbox linking to form.Sales) Customer Name (List Box linking to form.CustomerName + adding the info to form.Sale)

View 6 Replies View Related

General :: Create Check On A Field (customer ID) Of Customer Table In MS Access?

Sep 7, 2012

create a check on a feild(customer id) of customer table in MS Access , as "Customer ID is of 8 characters, the first 4 are alphabets and the last 4 are digits"

View 6 Replies View Related

General :: Auto Generate Unique Customer IDs

Aug 16, 2012

I am in the process of creating a DB for customer names, job numbers and document numbers, but have run into a problem right from the start.We are using a naming convention of the first 3 letters of the customer name, and 3 numbers after that. Eg. Customer names Allstar, Allpro would be All001 and All002 respectively.

The job numbers would then be sequential numbers, for each customer. So the first job for Allstar would be All001-001 and so on.How do I get Access to check/create the customer ID's? And then how do I get Access to check for the next job number for each customer?I was able to do this in Excel with the following;

=LEFT(B12,3)&COUNTIF($B$3:B12,LEFT(B12,3)&"*") in one cell
=IF(B12="","",LEFT(D12,3)&TEXT(RIGHT(D12,1),"00")) in another to add the preceding zero's.
Obviosuly , Excel is not the best way of tracking customers, jobs, documents etc.

View 14 Replies View Related

Forms :: Create Automatic Unique Number?

Apr 12, 2013

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?

View 10 Replies View Related

Modules & VBA :: Create New Counter On Customer Form For Each Day

Apr 6, 2015

I need Create a new counter on my Customer form for each day, using an Autonumber and print it

View 6 Replies View Related

Queries :: Create Query To List Hires By Customer Age Group

Jan 24, 2014

I am doing my project to create data base for Video Hire shop. Was allright so far; however hit the wall now.
I need to create query to list Hires(rentals) by customer age group. I have created query with the following fields: Customer ID, date (Date()), DOB field and calculating field: AgeGroup:Now()-[DOB]. When click to display data in AgeGroup field is displayed in days. How to set it up to display decades not number of days days?

View 1 Replies View Related

General :: Create Invoice Per Month Of Multiple Orders Per Customer?

Mar 19, 2013

My current database creates an invoice per order. Until now that worked wonderful. Now i want to create an invoice per month per customer with multiple orders on the same database. So i will have to create a new table/query on the existing order table.

View 1 Replies View Related

Unique Number Will Not Create

Jan 17, 2007

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???

View 6 Replies View Related

Create A Unique Identifier

Aug 22, 2006

Hi All.
Is there a way to index and create a unique identifier using a combination of fields (eg. User# + Provider + Date)?
Thanks.

View 1 Replies View Related

DMax To Create Unique ID

Dec 22, 2012

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...

View 4 Replies View Related

Tables :: How To Create A (unique) Field

Nov 12, 2012

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.

View 5 Replies View Related

Tables :: Combining Fields To Create Unique ID

Nov 14, 2013

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.

View 14 Replies View Related

How To Create All Unique Values In 4 X 4 Matrix Table

Oct 6, 2012

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.

View 6 Replies View Related

Queries :: Append Query - Unique Records To Create Relationship

Mar 22, 2015

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.

View 3 Replies View Related

Queries :: Loop To Create New Tables With Unique Field Values

Sep 20, 2013

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

View 5 Replies View Related

Combining Customer And Customer Contact Tables

Dec 24, 2011

I have a DB set up with a debtors table (Customers) and a Customer Contacts table, I was thinking of combining these two tables into one.Most of the Debtors are companies, but a few are actual people. With the current setup, i need to have both a debtor and a customer in two different tables, but with exactly the same data.

View 1 Replies View Related

Tables :: Merging 2 Fields Together To Create Additional Field - Unique References

Jul 23, 2015

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....

View 4 Replies View Related

Modules & VBA :: Create Unique Reference Number Based On Field Values For Record

Jun 18, 2015

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.

View 2 Replies View Related

Forms :: Add New Customer Form?

Apr 18, 2013

I am having problems with my frmAddCustomers. I have a qryNewUserNumber which works. I want the number generated by this query to appear in my unbound text field(txtNewNumber) on the form. Nomatter what I try it does not seem to work. Currently I keep getting a #Name? error

View 2 Replies View Related

Forms :: List All Records With Same Customer Name

Sep 11, 2013

Is there a way (via the form) to search for all the records that have the same customer name and have that list show up so the user can select the exact record s/he needs?

View 14 Replies View Related

Forms :: Combo Box Lookup - Edit Customer Information

Apr 24, 2013

I have a sales form that contains a combo box lookup to tblCUSTOMERS. It allows me to select a previous customer if that person is already entered into the system. If the person is NOT in the system, a form pops up to add the customer into the table.

What I need to know is, if the person IS already in the table BUT their address etc. has changed, what is the best way to get to that record to edit it? I'm THINKING a button with a command to go to that customer's profile.

What is the code to open a form to the customer selected in the drop down.

the customer name field is CUSTID

View 6 Replies View Related

Forms :: Enter The Docket Number To Retrieve Customer Information?

Mar 12, 2014

I am working on a database for a Skip Delivery Company, I have all relevant tables and Forms done and can add customers and so on. I have a field name in my Skips Delivered table called Delivery Docket which contains a long list of customer dockets. What i want to do is, be able to retrieve all customer information (such as Customer Name, County, Location, Phone Number and so on ...) on my Skips Delivered form by just entering the docket number on that form. I'm thinking i need an SQL Statement somewhere?

View 2 Replies View Related

Forms :: Updating All Of User Controlled Fields In Customer Table

Sep 23, 2013

I need to create a form that automatically populates Dealer_ID in my Customers table when a user selects Dealership from a dropdown box.

The form is updating all of the user controlled fields in the Customer table as the user inputs the information. I have a separate Dealership table who's Primary Key is Dealer_ID where dealership information (including the dealer name) is stored. A one to many relationship is in place that connects Dealer_ID from the Dealership table to the same field in the Customer table. The user inputting the information will know what the dealership name is but will not know their ID. I need a solution that will allow the user to select a dealership name in the form and auto-populate the Dealer_ID field in the Customer table with the appropriate ID from the Dealership table.

View 1 Replies View Related

Forms :: Duplicate Record Created Each Time In Customer Relationship

Sep 14, 2014

I am creating an access database for recording calls and follow ups for a company

relevant tables
mst_customers
card_call

ID from customers and customer_id from card_call have a one to many with all records of card_call

Although its more elaborate than this, lets get down to the problem

I created a form with a subform that should basically allow selection of customer and based on this, the creation of records in card_call as per customer selected. form and subform are linked by customer_id

Everything is working except a peculiar issue where if i select a customer_id it shows all relevant records + an additional record (usually 1 or 2) that just changes the customer_id for that record.

Example

customer_ID = 2 (combobox bound to column1 and showing name/column2)

output
call_ID | Customer_ID
1 | 2
2 | 2
3| 2

changing customer_id to 4 will change to
1 | 4
(new) |

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved