By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,098 Members | 1,120 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,098 IT Pros & Developers. It's quick & easy.

adding numerical values for ID purposes

P: n/a
Hello,
I have two fields (Customer and Product) which I need to track or assign an "ID number" to. For example, when Customer A has its first Product Y entry, then AY1. When Customer B has its first Product Y entry then BY1. When Customer A has its second Product Y entry, then AY2. How do I do this?? I tried the count function but that is not what I need as it returns only one numerical value for each customer...hence count. I need more of an ID for each line item.

Please help, Thanks!

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your concept goes agains the Normalization rules for relational
databases.

The value "AY1" violates the First Normal Form (1NF) rule: Each cell
should contain an atomic value. Atomic means only one data item per
cell. "AY1" contains 3 data items: Customer identifier, Product
identifier, and Sales Count of Product per Customer.

To track customer orders/buying trends see the Northwind database for
examples; specifically the Order and [Order Details] tables.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQflqxoechKqOuFEgEQJWNQCgoz3emXFLHhx6g1eKVB6wsD 5zENMAn0ti
ENJ+atYV3R9WUl1bi3NNsxx1
=o2pa
-----END PGP SIGNATURE-----
Kyle Friesen via AccessMonster.com wrote:
Hello,
I have two fields (Customer and Product) which I need to track or assign an "ID number" to. For example, when Customer A has its first Product Y entry, then AY1. When Customer B has its first Product Y entry then BY1. When Customer A has its second Product Y entry, then AY2. How do I do this?? I tried the count function but that is not what I need as it returns only one numerical value for each customer...hence count. I need more of an ID for each line item.

Nov 13 '05 #2

P: n/a
seems like you're storing more data about each product than needed, as
mentioned, but if you're dead set on doing it that way:

******Start Code*****

Dim strID as String

strID = Left(Me.txtCustomer.Value, 1) & Left(Me.txtProduct.Value, 1)
strID = strID & (Dcount("Products", "PurchaseTable", "Product Like ("""
& strID & "*"")" + 1)
Me.txtID.Value = strID

******End Code*****

where txtCustomer, txtProduct, and txtID are the names of the controls
that contain the relevant data.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.