If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding auto-numbered field screws up Table order
Each of my two tables has the same data except the last
five fields which are different. I'm simply trying to combine the tables for a Report by joining the two tables in a Query. Unfortunately, the order of the tables is different, and I need to use an auto-numbered field/Primary key because there are duplicates of similar data, but Access re-sorts the data in an uncomprehensible way when adding an autonumbered field. The use of this auto-numbered field as a Primary key or not, seems to make no difference, but I'm not sure. More specifically, I have a Customer Code (CC) field and a policy number (PN) field that are common to both Tables. In some cases there are multiple instances of the same CC and PN, so there are not unique records except the un- common data in the two tables. The data was exported from client software and multiple customer records are generated whenever more than one policy number is present. The good news is that both tables have the same number of records and can be matched up by sorting and saving the tables. This seems to provide good enough accuracy for my purposes since the exporting seems to keep the PN record order the same in both Tables when a similar CC is found, even though some CC's are in a different order in the Tables. Since I need to use something like a autonumbered field to provide unique records and have it report correctly, incorrect sort order messes up the data. If I import and have Access assign a primary key, wrong data shows up since the sort order is wrong. If I sort the Tables so they match, save them, then add a autonumbered field, Access somehow automatically resorts the Table before adding the auto-numbered field, even though it is quite close. Help! Example: Table Order CC PN Unique Table Data Table A SmithJ 100 $123.00 Table A SmithJ 100 $456.00 Table B SmithJ 100 Lowest Dollar Amt Table B SmithJ 100 Highest Dollar Amt Adding auto-numbered field might automatically switch the last two fields, so that Query results show: SmithJ 100 $123.00 Highest Dollar Amt SmithJ 100 $456.00 Lowest Dollar Amt |
#2
|
|||
|
|||
Adding auto-numbered field screws up Table order
Carl,
Assuming I understand you correctly, I think you will achieve your purpose by making a new blank table, with fields corresponding to the fields in the imported data, plus your AutoNumber field. Then, make a query, as you have already done, based on the imported table, and sorted in the required way, and then make it into an Append Query and copy the data into the blank table. The autonumber values should then reflect the order you want. -- Steve Schapel, Microsoft Access MVP Carl wrote: Each of my two tables has the same data except the last five fields which are different. I'm simply trying to combine the tables for a Report by joining the two tables in a Query. Unfortunately, the order of the tables is different, and I need to use an auto-numbered field/Primary key because there are duplicates of similar data, but Access re-sorts the data in an uncomprehensible way when adding an autonumbered field. The use of this auto-numbered field as a Primary key or not, seems to make no difference, but I'm not sure. More specifically, I have a Customer Code (CC) field and a policy number (PN) field that are common to both Tables. In some cases there are multiple instances of the same CC and PN, so there are not unique records except the un- common data in the two tables. The data was exported from client software and multiple customer records are generated whenever more than one policy number is present. The good news is that both tables have the same number of records and can be matched up by sorting and saving the tables. This seems to provide good enough accuracy for my purposes since the exporting seems to keep the PN record order the same in both Tables when a similar CC is found, even though some CC's are in a different order in the Tables. Since I need to use something like a autonumbered field to provide unique records and have it report correctly, incorrect sort order messes up the data. If I import and have Access assign a primary key, wrong data shows up since the sort order is wrong. If I sort the Tables so they match, save them, then add a autonumbered field, Access somehow automatically resorts the Table before adding the auto-numbered field, even though it is quite close. Help! Example: Table Order CC PN Unique Table Data Table A SmithJ 100 $123.00 Table A SmithJ 100 $456.00 Table B SmithJ 100 Lowest Dollar Amt Table B SmithJ 100 Highest Dollar Amt Adding auto-numbered field might automatically switch the last two fields, so that Query results show: SmithJ 100 $123.00 Highest Dollar Amt SmithJ 100 $456.00 Lowest Dollar Amt |
#3
|
|||
|
|||
Adding auto-numbered field screws up Table order
I'll try that, but before I do ... it seems like a lot of
extra steps particularly if I have to do this on a semi- annual basis or explain it to someone else. Also I don't yet know how to work with an Append Query. You would think Access would not mess with the order when adding an autonumber field. The data is fine and in the correct order when exported ... it's just Access that seems to mess with that order when adding the autonumbering, and I can manually resort and save in Access to get the correct order. It may be that your suggestion is the easiest way and I'll try it, but do you know of other database programs that are more straightforward? It seems that everytime I try to do simple or common things, like finding/isolating/deleting duplicates when merging databases, Access has a multi-step/non-user friendly way to accomplish it. Another example: If you set criteria for different fields in the same Query, the Query lines are duplicated and editing the query becomes cumbersome. I don't mean this to be a complaint board and certainly not to you, but I suspect you know something about different database products. Sorry for the lengthy note ... Carl -----Original Message----- Carl, Assuming I understand you correctly, I think you will achieve your purpose by making a new blank table, with fields corresponding to the fields in the imported data, plus your AutoNumber field. Then, make a query, as you have already done, based on the imported table, and sorted in the required way, and then make it into an Append Query and copy the data into the blank table. The autonumber values should then reflect the order you want. -- Steve Schapel, Microsoft Access MVP Carl wrote: Each of my two tables has the same data except the last five fields which are different. I'm simply trying to combine the tables for a Report by joining the two tables in a Query. Unfortunately, the order of the tables is different, and I need to use an auto-numbered field/Primary key because there are duplicates of similar data, but Access re- sorts the data in an uncomprehensible way when adding an autonumbered field. The use of this auto-numbered field as a Primary key or not, seems to make no difference, but I'm not sure. More specifically, I have a Customer Code (CC) field and a policy number (PN) field that are common to both Tables. In some cases there are multiple instances of the same CC and PN, so there are not unique records except the un- common data in the two tables. The data was exported from client software and multiple customer records are generated whenever more than one policy number is present. The good news is that both tables have the same number of records and can be matched up by sorting and saving the tables. This seems to provide good enough accuracy for my purposes since the exporting seems to keep the PN record order the same in both Tables when a similar CC is found, even though some CC's are in a different order in the Tables. Since I need to use something like a autonumbered field to provide unique records and have it report correctly, incorrect sort order messes up the data. If I import and have Access assign a primary key, wrong data shows up since the sort order is wrong. If I sort the Tables so they match, save them, then add a autonumbered field, Access somehow automatically resorts the Table before adding the auto-numbered field, even though it is quite close. Help! Example: Table Order CC PN Unique Table Data Table A SmithJ 100 $123.00 Table A SmithJ 100 $456.00 Table B SmithJ 100 Lowest Dollar Amt Table B SmithJ 100 Highest Dollar Amt Adding auto-numbered field might automatically switch the last two fields, so that Query results show: SmithJ 100 $123.00 Highest Dollar Amt SmithJ 100 $456.00 Lowest Dollar Amt . |
#4
|
|||
|
|||
Adding auto-numbered field screws up Table order
Carl,
Append Queries are very simple, and part of the standard methodology toolkit for manipulating data. You are trying to do a very unusual thing, so to me it is not surprising that there is no 1-step process to complete it. Access normally assumes a unique identifier in any set of data, which you do not have in your imported data, which to me reflects a fault in the imported data and not a fault with Access. Nevertheless, it is worth noting that data in a table has no "order", so a database program can't "mess with" the order. Put a bunch of apples in a bag, and then tip them out again, you might be lucky and have them drop in the order you put them in, but you wouldn't guarantee it. As regards your comment about "query lines are duplicated", to be honest I am not sure what you are referring to, but if you would care to post back with some more details and examples, I would be happy to respond. In my opinion, Access's query designer is one of the great features. I know of no desktop database application tool with the equivalent power of Access, which is more straightforward. Access is certainly a long way from perfect, but as far as I know, it is the best by a long way. -- Steve Schapel, Microsoft Access MVP Carl wrote: I'll try that, but before I do ... it seems like a lot of extra steps particularly if I have to do this on a semi- annual basis or explain it to someone else. Also I don't yet know how to work with an Append Query. You would think Access would not mess with the order when adding an autonumber field. The data is fine and in the correct order when exported ... it's just Access that seems to mess with that order when adding the autonumbering, and I can manually resort and save in Access to get the correct order. It may be that your suggestion is the easiest way and I'll try it, but do you know of other database programs that are more straightforward? It seems that everytime I try to do simple or common things, like finding/isolating/deleting duplicates when merging databases, Access has a multi-step/non-user friendly way to accomplish it. Another example: If you set criteria for different fields in the same Query, the Query lines are duplicated and editing the query becomes cumbersome. I don't mean this to be a complaint board and certainly not to you, but I suspect you know something about different database products. Sorry for the lengthy note ... Carl |
#5
|
|||
|
|||
Adding auto-numbered field screws up Table order
On Sat, 29 May 2004 15:24:32 -0700, "Carl"
wrote: Unfortunately, the order of the tables is different, and I need to use an auto-numbered field/Primary key because there are duplicates of similar data, but Access re-sorts the data in an uncomprehensible way when adding an autonumbered field. Part of the problem (and I'm treading on thin ice here, disagreeing with Steve!) is that you're assuming that a table HAS an order. It doesn't. It's an unordered "Bag" of data! Tables will often be presented for view in sequential order of the table's Primary Key, but if you use a Query joining this table to some other table, or use the table as the recordsource of a Report, or a variety of other uses, you'll see the records in whatever sequence the query optimizer finds most efficient. If you want to sort records in a particular order you must - no option! - use a Query rather than a table, and the Query must have an Order By clause sorting the records by specified fields in the table; or you must set the Order By property of a Form, or the Sorting and Grouping property of a Report to control the sequence. You *cannot* count on a table having ANY predictable order. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#6
|
|||
|
|||
Adding auto-numbered field screws up Table order
John,
If you read my responses carefully, I think you will find that, as is our wont, we are in perfect agreement on all points. ;-) -- Steve Schapel, Microsoft Access MVP John Vinson wrote: Part of the problem (and I'm treading on thin ice here, disagreeing with Steve!) is that you're assuming that a table HAS an order. It doesn't. It's an unordered "Bag" of data! Tables will often be presented for view in sequential order of the table's Primary Key, but if you use a Query joining this table to some other table, or use the table as the recordsource of a Report, or a variety of other uses, you'll see the records in whatever sequence the query optimizer finds most efficient. If you want to sort records in a particular order you must - no option! - use a Query rather than a table, and the Query must have an Order By clause sorting the records by specified fields in the table; or you must set the Order By property of a Form, or the Sorting and Grouping property of a Report to control the sequence. You *cannot* count on a table having ANY predictable order. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
Thread Tools | |
Display Modes | |
|
|