Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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! |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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" "CJ" wrote: 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! |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. "Sunday88310" wrote: 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" "CJ" wrote: 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! |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi There CJ
Thanks for your responce. I think I understand your example, When you tried [jobnum_Seqn] As "Index" What Happens? William<"M" "CJ" wrote: 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. "Sunday88310" wrote: 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" "CJ" wrote: 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! |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "Sunday88310" wrote: Hi There CJ Thanks for your responce. I think I understand your example, When you tried [jobnum_Seqn] As "Index" What Happens? William<"M" "CJ" wrote: 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. "Sunday88310" wrote: 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" "CJ" wrote: 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! |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Query won't let me get more than 95 fields | Excel Discussion (Misc queries) | |||
Query of an Access database won't let me get more than 95 fields. | Excel Discussion (Misc queries) | |||
Fields change order from MS Query to Excel | Excel Discussion (Misc queries) | |||
Microsoft Query SQL Concatenate | Excel Discussion (Misc queries) | |||
Query two fields | New Users to Excel |