Linking fields

[Guest]

I wish to create a query to retrieve data from a number of tables, the
problem is the field linking the tables is not in the same format for all of
them. I didn’t design the database so. The first table contains personal
details and uses the record number (autonumber) as the reference in a field
called Person No. There are two other fields, one called Product which is two
alphabetic characters and the other called Location which is 4 alphabetic
characters. Somehow the value in these three fields are added together in the
other tables to make up a text value in a field also called Person No, which
is the link between all of the tables. In other words if the Product is AB,
the Location is CDEF and the number is 100 the value in the field is
ABCDEF100. How do I create a query using the Person No field as a link? Is it
possible just to use the numeric part of the field.

 

[Steve Schapel]

Glen,

You can use a calculated field in a query to extract the Person No from
the concatenated data. It depends a bit on the structure of it. Going
on your example, if there are *always* 6 text characters before the
number...
PersonNumber: Val(Mid([Person No],7))

 

[Guest]

Steve,
I've tried that, it comes up with an error message when I try and save it.
'Syntax error (comma) in query expression [Table name].Val(Mid([Person
No],7))'
Am I doing this correctly? I have two tables in the query, the first
containg the Person No as a number and the second asa text field using 6 text
characters plus the number. The two tables are linked via this field and I am
using the calculated field with the second table.

Thanks for your help

Glen,

You can use a calculated field in a query to extract the Person No from
the concatenated data. It depends a bit on the structure of it. Going
on your example, if there are *always* 6 text characters before the
number...
PersonNumber: Val(Mid([Person No],7))

--
Steve Schapel, Microsoft Access MVP

I wish to create a query to retrieve data from a number of tables, the
problem is the field linking the tables is not in the same format for all of
them. I didn’t design the database so. The first table contains personal
details and uses the record number (autonumber) as the reference in a field
called Person No. There are two other fields, one called Product which is two
alphabetic characters and the other called Location which is 4 alphabetic
characters. Somehow the value in these three fields are added together in the
other tables to make up a text value in a field also called Person No, which
is the link between all of the tables. In other words if the Product is AB,
the Location is CDEF and the number is 100 the value in the field is
ABCDEF100. How do I create a query using the Person No field as a link? Is it
possible just to use the numeric part of the field.

 

[Guest]

I think I'm getting there, if I create a query on the second table to extract
the number and data and then link the query to the first table that works
ok. In which case would it be possible to do it the other way round. I have
quite a number of tables with the concatenated data and only one with the
number. It would save me a lot of work to write one query for the first table
and create a field containig concatenated dated that could then be linked to
all of the other tables.

Steve,
I've tried that, it comes up with an error message when I try and save it.
'Syntax error (comma) in query expression [Table name].Val(Mid([Person
No],7))'
Am I doing this correctly? I have two tables in the query, the first
containg the Person No as a number and the second asa text field using 6 text
characters plus the number. The two tables are linked via this field and I am
using the calculated field with the second table.

Thanks for your help

Glen,

You can use a calculated field in a query to extract the Person No from
the concatenated data. It depends a bit on the structure of it. Going
on your example, if there are *always* 6 text characters before the
number...
PersonNumber: Val(Mid([Person No],7))

--
Steve Schapel, Microsoft Access MVP

I wish to create a query to retrieve data from a number of tables, the
problem is the field linking the tables is not in the same format for all of
them. I didn’t design the database so. The first table contains personal
details and uses the record number (autonumber) as the reference in a field
called Person No. There are two other fields, one called Product which is two
alphabetic characters and the other called Location which is 4 alphabetic
characters. Somehow the value in these three fields are added together in the
other tables to make up a text value in a field also called Person No, which
is the link between all of the tables. In other words if the Product is AB,
the Location is CDEF and the number is 100 the value in the field is
ABCDEF100. How do I create a query using the Person No field as a link? Is it
possible just to use the numeric part of the field.

 

[John Spencer]

Try surrounding the entire table and field reference

Val(Mid([Your Table name].[Person No],7))

Or if the field name is unique in the query, then you could use the
following since there would be no ambiguity in the name.
Val(Mid([Person No],7))

Steve,
I've tried that, it comes up with an error message when I try and save it.
'Syntax error (comma) in query expression [Table name].Val(Mid([Person
No],7))'
Am I doing this correctly? I have two tables in the query, the first
containg the Person No as a number and the second asa text field using 6
text
characters plus the number. The two tables are linked via this field and I
am
using the calculated field with the second table.

Thanks for your help

Glen,

You can use a calculated field in a query to extract the Person No from
the concatenated data. It depends a bit on the structure of it. Going
on your example, if there are *always* 6 text characters before the
number...
PersonNumber: Val(Mid([Person No],7))

--
Steve Schapel, Microsoft Access MVP

I wish to create a query to retrieve data from a number of tables, the
problem is the field linking the tables is not in the same format for
all of
them. I didn't design the database so. The first table contains
personal
details and uses the record number (autonumber) as the reference in a
field
called Person No. There are two other fields, one called Product which
is two
alphabetic characters and the other called Location which is 4
alphabetic
characters. Somehow the value in these three fields are added together
in the
other tables to make up a text value in a field also called Person No,
which
is the link between all of the tables. In other words if the Product
is AB,
the Location is CDEF and the number is 100 the value in the field is
ABCDEF100. How do I create a query using the Person No field as a link?
Is it
possible just to use the numeric part of the field.