Storing two numbers, separated by commas, in one field formatted as anumber?

[R Tanner]

Is that possible? I want to store two numbers in one field and format
it as a number. Or maybe I should just format it as text? Is there
any downside? Well, I know there is. My ability to do statistics is
greatly reduced. Other than that though...

 

[Allen Browne]

Is that possible? I want to store two numbers in one field and format
it as a number. Or maybe I should just format it as text? Is there
any downside? Well, I know there is. My ability to do statistics is
greatly reduced. Other than that though...

Use 2 fields.

One of the basic rules of data normalization is that the data must be
atomic. That means you store only one thing in a field. So you never put 2
or more values in the one field in a database.

 

[R Tanner]

Use 2 fields.

One of the basic rules of data normalization is that the data must be
atomic. That means you store only one thing in a field. So you never put 2
or more values in the one field in a database.

Okay but that raises the question of what if there are 3 account
numbers? This field lists the accounts that are affected in Excel.
What would you suggest I do in that case? I read something about
being able to select two different options from a combo box when you
are doing a lookup column, so is that a completely different concept?
Why would you be able to enter two fields in that case, but not in
this case?

 

[R Tanner]

What do the "two numbers" represent?

Will there ever be more than two?

What do you want/need to do with the data, once stored?

"formatted as a number" - no, it would have to be text (but see Allen's
response about the inadvisability of storing two facts in one field).

More info, please...

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/






- Show quoted text -

This database will track tickets previously tracked in Excel. The
numbers represent client accounts that were affected by the issue.
This is a variable number of accounts - typically it will be just one,
but it can be more.

If I wanted to query it, I could probably just store it as text and
then write code to search for the comma in each field and if it is
found then consider the values on each side of the comma, right?
That seems like alot of extraness...Maybe I should just have a
separate record for each affected account?

 

[Allen Browne]

So one client(?) can have multiple accounts?

In a relational database, you create a related table. Each account is a row
on its own, and you have a ClientID field to tell you which client this
account is for.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Use 2 fields.

One of the basic rules of data normalization is that the data must be
atomic. That means you store only one thing in a field. So you never put 2
or more values in the one field in a database.

Okay but that raises the question of what if there are 3 account
numbers? This field lists the accounts that are affected in Excel.
What would you suggest I do in that case? I read something about
being able to select two different options from a combo box when you
are doing a lookup column, so is that a completely different concept?
Why would you be able to enter two fields in that case, but not in
this case?

 

[nouveauricheinvestments]

So one client(?) can have multiple accounts?

In a relational database, you create a related table. Each account is a row
on its own, and you have a ClientID field to tell you which client this
account is for.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.






Okay but that raises the question of what if there are 3 account
numbers? This field lists the accounts that are affected in Excel.
What would you suggest I do in that case? I read something about
being able to select two different options from a combo box when you
are doing a lookup column, so is that a completely different concept?
Why would you be able to enter two fields in that case, but not in
this case?

Yes, each client typically has multiple accounts. However, the
frequency that multiple accounts are affected in the scheme of things
is relatively small...I think it would probably be best if I just have
a separate record for each account. That would probably be best..


This is a small problem compared to my other post. If you guys get a
chance take a look at it. I want to create a parent table on issues.
These issues change from time to time. Two years ago, the company
would just type in whatever, without a set value. Consequently, I
have a list of like 300 different issues I have to figure out how to
normalize.

Now, we have a series of about 10 different issues selected from a
drop down.

 

[nouveauricheinvestments]

Yes, each client typically has multiple accounts. However, the
frequency that multiple accounts are affected in the scheme of things
is relatively small...I think it would probably be best if I just have
a separate record for each account. That would probably be best..

This is a small problem compared to my other post. If you guys get a
chance take a look at it. I want to create a parent table on issues.
These issues change from time to time. Two years ago, the company
would just type in whatever, without a set value. Consequently, I
have a list of like 300 different issues I have to figure out how to
normalize.

Now, we have a series of about 10 different issues selected from a
drop down.

By the way, that thread is called 'Tables with relationships and
Changing variables'

 

[Jeff Boyce]

What do the "two numbers" represent?

Will there ever be more than two?

What do you want/need to do with the data, once stored?

"formatted as a number" - no, it would have to be text (but see Allen's
response about the inadvisability of storing two facts in one field).

More info, please...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jeff Boyce]

I'm still not clear on what "numbers" you are storing. Are you saying
"ticket numbers"? If so, they aren't really "numbers", are they?! You
won't be adding/subtracting/multiplying/dividing those "numbers", right? So
they are probably actually characters (text) that happen to be digits.

If you have a one-to-many relationship, and if you want to get good use of
Access' relationally-oriented features and functions, don't try feeding
Access 'sheet data. Access is not a spreadsheet on steroids. You'll need
to think about your data in new ways.

Can one account be related to multiple tickets? Can one ticket be related
to multiple accounts? If so, you have a MANY-TO-MANY relationship! That
takes three tables to resolve: tblTickets, tblAccounts, trelTicketAccount.

More info, please!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

What do the "two numbers" represent?

Will there ever be more than two?

What do you want/need to do with the data, once stored?

"formatted as a number" - no, it would have to be text (but see Allen's
response about the inadvisability of storing two facts in one field).

More info, please...

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/






- Show quoted text -

This database will track tickets previously tracked in Excel. The
numbers represent client accounts that were affected by the issue.
This is a variable number of accounts - typically it will be just one,
but it can be more.

If I wanted to query it, I could probably just store it as text and
then write code to search for the comma in each field and if it is
found then consider the values on each side of the comma, right?
That seems like alot of extraness...Maybe I should just have a
separate record for each affected account?

 

[Ken Sheridan]

What you seem to have here is many-to-many relationship type between accounts
and issues, i.e. each accounts might be affected by one or more issues and
each issue might affect one or more accounts. In a relational database a
many-to-many relationship type is modelled by a table which resolves the
relationship into two one-to-many relationship types. So you'd have a table,
AccountIssues say, with two foreign keys AccountNumber and Issue. You might
also have other non-key columns in this table such as DateAffected etc.

If Account 123 is affected by issues 5, 7 and 9 there'd be three rows in the
AccountIssues table:

123 5
123 7
123 9

If issue 7 also affected accounts 345 and 678 then there'd be two more rows,
so you'd have:

123 5
123 7
123 9
345 7
678 7

I've used a number for the issue column here, but if you read my response to
your other thread you'll see it would actually be a text column for the
reasons given there. This table would relate to the issues and accounts
table. The latter would have one line per account and would in turn probably
relate to a clients table, so the relationships would look like this:

Clients----<Accounts----<AccountIssues>----Issues

where the < and > signs represent the 'many' end of each relationship.
There are likely to be other tables around this core model of course.

Querying a model like that above is very simple.

Ken Sheridan
Stafford, England

 

[Allen Browne]

Yes, each client typically has multiple accounts. However, the
frequency that multiple accounts are affected in the scheme of things
is relatively small...I think it would probably be best if I just have
a separate record for each account. That would probably be best..

That may be adequate. You're in the best postion to decide whether you care
only about accounts (in which case a separate record for each account is all
you need), or if you care about distinct clients (e.g. to choose top clients
where the amounts could be in 2 or more accounts.)