Combining two number fields into one

[DJ]

I've got a number field in a table with two digits ("08"). I've got another
number field in the same table with four digits ("1234"). Is there a way to
combine these into a six digit field ("081234")? And then - is there a way
to do a one-to-many match of this configured six-digit number to a six-digit
number from another table?

 

[Wayne-I-M]

Hi

Simple answer No.

The 2 "numbers" you have are not numbers they are text (well the second may
be but the 1st isn't) numbers can't start with 0. So your 08 (if it was a
number would be just 8.

Would you can is to concentate the text fields then convert this to a number
(you can do this in the same function) but dont forget you will lose any
preceeding 0's.

Next bit - Yes you can simple do a drag and drop in the relationship window
to create the One-to-Many but to get a better answer on that you'd need to
give more details. But the basics are that as long as the field (in each
table) are the same number / text / etc) there should not be problem - but
(again) don't forget that your are dealing with a "text" field and not a
number if you have a precceding 0.

 

[DJ]

Bad example. I do understand the preceeding zero thing. Let's say my first
number is "12". I can figure out how to do it in a query. But can I do this
"new" table within the original table itself? Or can you only do a
calculated field within a table if it's pulling data from a DIFFERENT table?

 

[DJ]

Bad example. Let's say the first number is "12".

I can figure out how to combine the two numbers into one within a query.
But can I do it in a table? Or can you only do calculated fields in a table
if it's pulling the fields from a DIFFERENT table?

 

[Jeff Boyce]

A point of clarification, following on Wayne's response...

You are calling them "numbers". Is it possible that they are actually
identifiers or codes that happen to be digits? If that's the case, Wayne's
comment about text is your solution.

Stop thinking about them as "numbers" and start treating them as text.

Yes, you can concatenate two text fields in a query to get a new
(concatenated) field, then use that query to compare that new (text) field
to another table's text field.

.... or have I misinterpreted your description?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Wayne-I-M]

Sorry - you lost me on that.

Have a look at Jeff's answer 1st and see if that is what you're looking for.

To answer this question (sorry about) agai the answer is No. You only
(should) use a table for storing data - as they say up "nowt" else.

Add a new field you some
Create a query
Concencate the 2 fields (whatever they)
Convert the query to an update
Update the new field you just made
Try doing you relationship thing with this
Does this work ??

If not then post back

 

[BruceM]

You can't calculate a field directly in a table, if that is what you are
asking. However, I expect you could calculate the field in a query, then
join the query to another query or to a table by way of that field.

 

[DJ]

I combined the two with a calculated field on the form. Thanks.