Cannot Concatenate fields using Query

[Guest]

After much searching and not finding a solution I hope that someone can share
some pearls of wisdom. I have a single table (multiple fields) containing a
job number and sequence number. I simply wish to create a new field that has
the job number and sequence number combined. Hmmm sounds simple enough, but I
am stumped! Job #1000 seq #1 would become 1000-1

I wish to use this new field as a unique index qualifier against a
transaction table.

Even though I have searched the discussion group boards, and have tried some
of the solutions, nothing works.

I am using Excel 2002, and hitting a FoxPro database.

Any help, guidance would be greatly appreciated. Thank you in advance!

 

[Guest]

I wish to use this new field as a unique index qualifier against a
transaction table.
-------------------------------------------------------------------------------------
You can't create a new qualifier (Primary Key) that I know of, simply by a
Query
Creating a new unique qualifier (Primary Key) at this point?
Are you talking about the Primary Key?
What has been the unique qualifier (Primary Key) to this point? Auto
Numbering?
Most data bases are not structured in a way that allows the changing of
unique qualifiers after construction is complete.
I don't know in FoxPro if it can be done, If it were me, I would build a
new data base (ground up) and then import the existing records into the new
data base, through excel, or create new froms in FoxPro for adding records
with the new qualifier, then the records could take on the new unique
qualifier (Primary Key) in the methods and the limitations of your choice.
Its been my experience that altering an existing Primary Key can produce
unexpected results, if it can be done at all.
I'm not familiar at all with FoxPro only Access construction, so I can't
offer too much insight on any of the FoxPro construction. I would request
information from FoxPro about changing the data base Primary key before
altering it.
I would very much like for you to let me know through this thread, all your
findings on making the new alterations.

 

[Guest]

I am sorry I did not 'qualify' my problem with excel Query.

Here is the SQL Statement

SELECT prtime.jobnum, prtime.seqn, [jobnum & seqn] AS 'Index'
FROM prtime prtime
ORDER BY prtime.jobnum, prtime.seqn
Concatenate fields Query

Here is the output
Job # Seq Not what I want this is what I want

30261 1 jobnum & seqn 30261 1
30261 2 jobnum & seqn 30261 2
30261 3 jobnum & seqn 30261 3
30261 4 jobnum & seqn 30261 4
30261 5 jobnum & seqn 30261 5

It would appear to be so simple, and I have tried different syntx but to no
avail.

 

[Guest]

Hi There CJ
Thanks for your responce.
I think I understand your example,

When you tried [jobnum_Seqn] As "Index"

What Happens?
William<"M"

I am sorry I did not 'qualify' my problem with excel Query.

Here is the SQL Statement

SELECT prtime.jobnum, prtime.seqn, [jobnum & seqn] AS 'Index'
FROM prtime prtime
ORDER BY prtime.jobnum, prtime.seqn
Concatenate fields Query

Here is the output
Job # Seq Not what I want this is what I want

30261 1 jobnum & seqn 30261 1
30261 2 jobnum & seqn 30261 2
30261 3 jobnum & seqn 30261 3
30261 4 jobnum & seqn 30261 4
30261 5 jobnum & seqn 30261 5

It would appear to be so simple, and I have tried different syntx but to no
avail.

I wish to use this new field as a unique index qualifier against a
transaction table.
-------------------------------------------------------------------------------------
You can't create a new qualifier (Primary Key) that I know of, simply by a
Query
Creating a new unique qualifier (Primary Key) at this point?
Are you talking about the Primary Key?
What has been the unique qualifier (Primary Key) to this point? Auto
Numbering?
Most data bases are not structured in a way that allows the changing of
unique qualifiers after construction is complete.
I don't know in FoxPro if it can be done, If it were me, I would build a
new data base (ground up) and then import the existing records into the new
data base, through excel, or create new froms in FoxPro for adding records
with the new qualifier, then the records could take on the new unique
qualifier (Primary Key) in the methods and the limitations of your choice.
Its been my experience that altering an existing Primary Key can produce
unexpected results, if it can be done at all.
I'm not familiar at all with FoxPro only Access construction, so I can't
offer too much insight on any of the FoxPro construction. I would request
information from FoxPro about changing the data base Primary key before
altering it.
I would very much like for you to let me know through this thread, all your
findings on making the new alterations.

 

[Guest]

Hi William, the query will list each unique job number and seqence number
then for each line the query adds "jobnum & seqn" as text for each row.
jobnum seqn index
30261 1 jobnum & seqn
30261 2 jobnum & seqn
30261 3 jobnum & seqn
30261 4 jobnum & seqn

Again the query is as follows;
SELECT prtime.jobnum, prtime.seqn, [jobnum & seqn] AS 'Index'
FROM prtime prtime
ORDER BY prtime.jobnum, prtime.seqn


What I am trying for is

jobnum seqn index
30261 1 30261 1
30261 2 30261 2
30261 3 30261 3
30261 4 30261 4

I have spent a considerable amount of time simply looking for examples on
concatenating two text fields, so far no matter what I try does not work.

Any suggestions???

Thank you!!

CJ

Hi There CJ
Thanks for your responce.
I think I understand your example,

When you tried [jobnum_Seqn] As "Index"

What Happens?
William<"M"

I am sorry I did not 'qualify' my problem with excel Query.

Here is the SQL Statement

SELECT prtime.jobnum, prtime.seqn, [jobnum & seqn] AS 'Index'
FROM prtime prtime
ORDER BY prtime.jobnum, prtime.seqn
Concatenate fields Query

Here is the output
Job # Seq Not what I want this is what I want

30261 1 jobnum & seqn 30261 1
30261 2 jobnum & seqn 30261 2
30261 3 jobnum & seqn 30261 3
30261 4 jobnum & seqn 30261 4
30261 5 jobnum & seqn 30261 5

It would appear to be so simple, and I have tried different syntx but to no
avail.

I wish to use this new field as a unique index qualifier against a
transaction table.
-------------------------------------------------------------------------------------
You can't create a new qualifier (Primary Key) that I know of, simply by a
Query
Creating a new unique qualifier (Primary Key) at this point?
Are you talking about the Primary Key?
What has been the unique qualifier (Primary Key) to this point? Auto
Numbering?
Most data bases are not structured in a way that allows the changing of
unique qualifiers after construction is complete.
I don't know in FoxPro if it can be done, If it were me, I would build a
new data base (ground up) and then import the existing records into the new
data base, through excel, or create new froms in FoxPro for adding records
with the new qualifier, then the records could take on the new unique
qualifier (Primary Key) in the methods and the limitations of your choice.
Its been my experience that altering an existing Primary Key can produce
unexpected results, if it can be done at all.
I'm not familiar at all with FoxPro only Access construction, so I can't
offer too much insight on any of the FoxPro construction. I would request
information from FoxPro about changing the data base Primary key before
altering it.
I would very much like for you to let me know through this thread, all your
findings on making the new alterations.
--
William<"M"




:

After much searching and not finding a solution I hope that someone can share
some pearls of wisdom. I have a single table (multiple fields) containing a
job number and sequence number. I simply wish to create a new field that has
the job number and sequence number combined. Hmmm sounds simple enough, but I
am stumped! Job #1000 seq #1 would become 1000-1

I wish to use this new field as a unique index qualifier against a
transaction table.

Even though I have searched the discussion group boards, and have tried some
of the solutions, nothing works.

I am using Excel 2002, and hitting a FoxPro database.

Any help, guidance would be greatly appreciated. Thank you in advance!

 

[Guest]

This is my E-Mail Address (e-mail address removed)
Please send me an email and let me know how your input mask on your database
key Field was created. Thanks
Hopefully I can help

William<"M"

Hi William, the query will list each unique job number and seqence number
then for each line the query adds "jobnum & seqn" as text for each row.
jobnum seqn index
30261 1 jobnum & seqn
30261 2 jobnum & seqn
30261 3 jobnum & seqn
30261 4 jobnum & seqn

Again the query is as follows;
SELECT prtime.jobnum, prtime.seqn, [jobnum & seqn] AS 'Index'
FROM prtime prtime
ORDER BY prtime.jobnum, prtime.seqn


What I am trying for is

jobnum seqn index
30261 1 30261 1
30261 2 30261 2
30261 3 30261 3
30261 4 30261 4

I have spent a considerable amount of time simply looking for examples on
concatenating two text fields, so far no matter what I try does not work.

Any suggestions???

Thank you!!

CJ

Hi There CJ
Thanks for your responce.
I think I understand your example,

When you tried [jobnum_Seqn] As "Index"

What Happens?
William<"M"

I am sorry I did not 'qualify' my problem with excel Query.

Here is the SQL Statement

SELECT prtime.jobnum, prtime.seqn, [jobnum & seqn] AS 'Index'
FROM prtime prtime
ORDER BY prtime.jobnum, prtime.seqn
Concatenate fields Query

Here is the output
Job # Seq Not what I want this is what I want

30261 1 jobnum & seqn 30261 1
30261 2 jobnum & seqn 30261 2
30261 3 jobnum & seqn 30261 3
30261 4 jobnum & seqn 30261 4
30261 5 jobnum & seqn 30261 5

It would appear to be so simple, and I have tried different syntx but to no
avail.






:

I wish to use this new field as a unique index qualifier against a
transaction table.
-------------------------------------------------------------------------------------
You can't create a new qualifier (Primary Key) that I know of, simply by a
Query
Creating a new unique qualifier (Primary Key) at this point?
Are you talking about the Primary Key?
What has been the unique qualifier (Primary Key) to this point? Auto
Numbering?
Most data bases are not structured in a way that allows the changing of
unique qualifiers after construction is complete.
I don't know in FoxPro if it can be done, If it were me, I would build a
new data base (ground up) and then import the existing records into the new
data base, through excel, or create new froms in FoxPro for adding records
with the new qualifier, then the records could take on the new unique
qualifier (Primary Key) in the methods and the limitations of your choice.
Its been my experience that altering an existing Primary Key can produce
unexpected results, if it can be done at all.
I'm not familiar at all with FoxPro only Access construction, so I can't
offer too much insight on any of the FoxPro construction. I would request
information from FoxPro about changing the data base Primary key before
altering it.
I would very much like for you to let me know through this thread, all your
findings on making the new alterations.
--
William<"M"




:

After much searching and not finding a solution I hope that someone can share
some pearls of wisdom. I have a single table (multiple fields) containing a
job number and sequence number. I simply wish to create a new field that has
the job number and sequence number combined. Hmmm sounds simple enough, but I
am stumped! Job #1000 seq #1 would become 1000-1

I wish to use this new field as a unique index qualifier against a
transaction table.

Even though I have searched the discussion group boards, and have tried some
of the solutions, nothing works.

I am using Excel 2002, and hitting a FoxPro database.

Any help, guidance would be greatly appreciated. Thank you in advance!

 

[Guest]

OK here is my solution to concatenate two fields, one text and the other
numeric. In this example I have converted the numeric value to a character.

SELECT jobnum, seqn, jobnum + STR(seqn,5,1) AS Index
FROM prtime
ORDER BY jobnum, seqn
Regards,

CJ

This is my E-Mail Address (e-mail address removed)
Please send me an email and let me know how your input mask on your database
key Field was created. Thanks
Hopefully I can help

William<"M"

Hi William, the query will list each unique job number and seqence number
then for each line the query adds "jobnum & seqn" as text for each row.
jobnum seqn index
30261 1 jobnum & seqn
30261 2 jobnum & seqn
30261 3 jobnum & seqn
30261 4 jobnum & seqn

Again the query is as follows;
SELECT prtime.jobnum, prtime.seqn, [jobnum & seqn] AS 'Index'
FROM prtime prtime
ORDER BY prtime.jobnum, prtime.seqn


What I am trying for is

jobnum seqn index
30261 1 30261 1
30261 2 30261 2
30261 3 30261 3
30261 4 30261 4

I have spent a considerable amount of time simply looking for examples on
concatenating two text fields, so far no matter what I try does not work.

Any suggestions???

Thank you!!

CJ

Hi There CJ
Thanks for your responce.
I think I understand your example,

When you tried [jobnum_Seqn] As "Index"

What Happens?
William<"M"




:

I am sorry I did not 'qualify' my problem with excel Query.

Here is the SQL Statement

SELECT prtime.jobnum, prtime.seqn, [jobnum & seqn] AS 'Index'
FROM prtime prtime
ORDER BY prtime.jobnum, prtime.seqn
Concatenate fields Query

Here is the output
Job # Seq Not what I want this is what I want

30261 1 jobnum & seqn 30261 1
30261 2 jobnum & seqn 30261 2
30261 3 jobnum & seqn 30261 3
30261 4 jobnum & seqn 30261 4
30261 5 jobnum & seqn 30261 5

It would appear to be so simple, and I have tried different syntx but to no
avail.






:

I wish to use this new field as a unique index qualifier against a
transaction table.
-------------------------------------------------------------------------------------
You can't create a new qualifier (Primary Key) that I know of, simply by a
Query
Creating a new unique qualifier (Primary Key) at this point?
Are you talking about the Primary Key?
What has been the unique qualifier (Primary Key) to this point? Auto
Numbering?
Most data bases are not structured in a way that allows the changing of
unique qualifiers after construction is complete.
I don't know in FoxPro if it can be done, If it were me, I would build a
new data base (ground up) and then import the existing records into the new
data base, through excel, or create new froms in FoxPro for adding records
with the new qualifier, then the records could take on the new unique
qualifier (Primary Key) in the methods and the limitations of your choice.
Its been my experience that altering an existing Primary Key can produce
unexpected results, if it can be done at all.
I'm not familiar at all with FoxPro only Access construction, so I can't
offer too much insight on any of the FoxPro construction. I would request
information from FoxPro about changing the data base Primary key before
altering it.
I would very much like for you to let me know through this thread, all your
findings on making the new alterations.
--
William<"M"




:

After much searching and not finding a solution I hope that someone can share
some pearls of wisdom. I have a single table (multiple fields) containing a
job number and sequence number. I simply wish to create a new field that has
the job number and sequence number combined. Hmmm sounds simple enough, but I
am stumped! Job #1000 seq #1 would become 1000-1

I wish to use this new field as a unique index qualifier against a
transaction table.

Even though I have searched the discussion group boards, and have tried some
of the solutions, nothing works.

I am using Excel 2002, and hitting a FoxPro database.

Any help, guidance would be greatly appreciated. Thank you in advance!