Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-13-2018, 08:19 AM   #1
Leshunter
Newly Registered User
 
Join Date: Nov 2017
Location: Newent Gloucestershire UK
Posts: 13
Thanks: 18
Thanked 0 Times in 0 Posts
Leshunter is on a distinguished road
need help with autonumber and number fields

Hi All
I am working on a database where I need to have two fields produce the same end result
field 1 CustomerID is autonumber
field 2 RecordID is number

I would like to have field 2 produce the same result as field 1

If this is possible could someone please point me in the right direction
PS I am very new to Access and still finding my way around so please be kind

many thanks
Les Hunter

Leshunter is offline   Reply With Quote
Old 03-13-2018, 08:41 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,136
Thanks: 54
Thanked 1,968 Times in 1,881 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: need help with autonumber and number fields

Maybe possible on using data macro before update you assign custno to recordno
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 03-13-2018, 08:42 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,637
Thanks: 10
Thanked 2,071 Times in 2,026 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: need help with autonumber and number fields

Why?

You shouldn't store redundant data in a database. If you want the value of CustomerID, then use the value of CustomerID.

plog is offline   Reply With Quote
Old 03-13-2018, 09:01 AM   #4
Leshunter
Newly Registered User
 
Join Date: Nov 2017
Location: Newent Gloucestershire UK
Posts: 13
Thanks: 18
Thanked 0 Times in 0 Posts
Leshunter is on a distinguished road
Re: need help with autonumber and number fields

Hi both
Thanks for the quick responses
I need to use the value of RecordID in a search on a form which requires the RecordID and the same ID to automatically populate the CustomerID field when creating a new record saving having to input it manually

once again thanks for you responses
Les hunter
Leshunter is offline   Reply With Quote
Old 03-13-2018, 09:11 AM   #5
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,585
Thanks: 10
Thanked 1,249 Times in 1,189 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Quote:
Originally Posted by Leshunter View Post
Hi All
I am working on a database where I need to have two fields produce the same end result
field 1 CustomerID is autonumber
field 2 RecordID is number

I would like to have field 2 produce the same result as field 1
The best way to do this it is to use a query. Only have the first field in the table, then write a query as follows...
Code:
SELECT CustomerID, CustomerID As RecordID
FROM  Table
But regardless, it is still a duplication that makes no sense to me to do.
hth
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
Leshunter (03-13-2018)
Old 03-13-2018, 09:17 AM   #6
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,637
Thanks: 10
Thanked 2,071 Times in 2,026 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: need help with autonumber and number fields

Yeah, not really following you:

Quote:
I need to use the value of RecordID in a search on a form...
That value is equivalent to CustomerID, so you would use its value.

Quote:
...and the same ID to automatically populate the CustomerID field when creating a new record saving
That makes absolutely no sense. Since its an autonumber, you cannot ensure any specific value will populate in the CustomerID field.

You really need to step back, give us a good big picture explanation adn then what you are trying to accomplish in this particular instance.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Leshunter (03-13-2018)
Old 03-13-2018, 09:25 AM   #7
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,421
Thanks: 51
Thanked 940 Times in 910 Posts
gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light
Re: need help with autonumber and number fields

Quote:
field 1 CustomerID is autonumber
field 2 RecordID is number
You may be missing something.

In your customer table, you need a unique identifier. So by all means have an autonumber ID field. You might then also have a customer account number (alpha numeric - like S001), and a customer name, which could be unique, but may not be. Company names will be unique, but Individual names won't be. You may have more than one Mr J Smith.

Having an account number as well as the autonumber may be a bit of overkill, but it's still useful.

What you then have is , say, an orders table.
In this table, you have an autonumber field for the record id for the order, and a number field for the customer ID. The value here corresponds to the customers autonumber, but it's not an autonumber in the orders table.

This way, your customer table is related to the orders table, so you can find all the orders for a given customer, or equally, the customer for a given order.

I hope that makes sense.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
The Following User Says Thank You to gemma-the-husky For This Useful Post:
Leshunter (03-13-2018)
Old 03-13-2018, 10:46 AM   #8
Leshunter
Newly Registered User
 
Join Date: Nov 2017
Location: Newent Gloucestershire UK
Posts: 13
Thanks: 18
Thanked 0 Times in 0 Posts
Leshunter is on a distinguished road
Re: need help with autonumber and number fields

Thanks all.
I understand now a little bit more about redundancy and how to construct my tables so I'm going to start over again.
Thanks to Dave(aka Gemma the Husky) for your guidance,
thanks to Plog for your patience ,
and thanks to MarKK for your idea of a query ,
I will (I promise) to look at all the advice in my re-run
your as ever grateful
Les Hunter

Leshunter is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert AutoNumber to Number Bill Bisco Queries 2 04-28-2009 06:58 AM
How to increment the autonumber (Record Number) after that some fields were filled? Chipcom Modules & VBA 4 04-10-2007 08:10 PM
Autonumber has skipped a number- why? lodgey47 Tables 3 01-19-2006 08:47 AM
Covert number to autonumber richardplr Tables 5 03-31-2004 05:34 PM
[SOLVED] Number Increment without Autonumber...HELP!!!! Catarina Forms 2 10-12-2001 03:04 AM




All times are GMT -8. The time now is 01:00 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World