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
|
|||
|
|||
Data Mysteriously Changes
I have a db set up to provide our customers with information pertaining to
vehicle maintenance. I import data from another program into a table (VEH). The only customer information this table has is coded. I have a query that gathers data from this table and another table (ORG) giving cleartext customer names to facilitate easier customer interaction. the ORG table has the following fields: ID NAME j1 Johnson, Mark In my import macro, I run a delete query to empty the VEH table, then import from an ASCII file to repopulate the table. The macro only modifies the data in the VEH table. However, whenever I open the nested form to view the data, sometimes data is shown that doesn't belong. I check the ORG table, and names have been moved around or duplicated under the wrong codes. This doesn't appear to be a problem with the macro or any of the queries. Is there something going on with Access 2007 I should be aware of? I used the same file under 2003 and never had an issue. Sorry for being so verbose, but wanted to get as much info as possible in to help you help me. Thanks. |
#2
|
|||
|
|||
Data Mysteriously Changes
Do you have Cascading Updates and/or Cascading Deletes turned on for the
table relationships? Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... I have a db set up to provide our customers with information pertaining to vehicle maintenance. I import data from another program into a table (VEH). The only customer information this table has is coded. I have a query that gathers data from this table and another table (ORG) giving cleartext customer names to facilitate easier customer interaction. the ORG table has the following fields: ID NAME j1 Johnson, Mark In my import macro, I run a delete query to empty the VEH table, then import from an ASCII file to repopulate the table. The macro only modifies the data in the VEH table. However, whenever I open the nested form to view the data, sometimes data is shown that doesn't belong. I check the ORG table, and names have been moved around or duplicated under the wrong codes. This doesn't appear to be a problem with the macro or any of the queries. Is there something going on with Access 2007 I should be aware of? I used the same file under 2003 and never had an issue. Sorry for being so verbose, but wanted to get as much info as possible in to help you help me. Thanks. |
#3
|
|||
|
|||
Data Mysteriously Changes
No, I have no relationships set. I have a join in the query that returns the
NAME field from the ORG table where the ID fields are equal. It only returns data where both sides are equal. "Jeff Boyce" wrote: Do you have Cascading Updates and/or Cascading Deletes turned on for the table relationships? Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... I have a db set up to provide our customers with information pertaining to vehicle maintenance. I import data from another program into a table (VEH). The only customer information this table has is coded. I have a query that gathers data from this table and another table (ORG) giving cleartext customer names to facilitate easier customer interaction. the ORG table has the following fields: ID NAME j1 Johnson, Mark In my import macro, I run a delete query to empty the VEH table, then import from an ASCII file to repopulate the table. The macro only modifies the data in the VEH table. However, whenever I open the nested form to view the data, sometimes data is shown that doesn't belong. I check the ORG table, and names have been moved around or duplicated under the wrong codes. This doesn't appear to be a problem with the macro or any of the queries. Is there something going on with Access 2007 I should be aware of? I used the same file under 2003 and never had an issue. Sorry for being so verbose, but wanted to get as much info as possible in to help you help me. Thanks. |
#4
|
|||
|
|||
Data Mysteriously Changes
If you post the SQL statement, folks here might be able to offer their
observations. Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... No, I have no relationships set. I have a join in the query that returns the NAME field from the ORG table where the ID fields are equal. It only returns data where both sides are equal. "Jeff Boyce" wrote: Do you have Cascading Updates and/or Cascading Deletes turned on for the table relationships? Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... I have a db set up to provide our customers with information pertaining to vehicle maintenance. I import data from another program into a table (VEH). The only customer information this table has is coded. I have a query that gathers data from this table and another table (ORG) giving cleartext customer names to facilitate easier customer interaction. the ORG table has the following fields: ID NAME j1 Johnson, Mark In my import macro, I run a delete query to empty the VEH table, then import from an ASCII file to repopulate the table. The macro only modifies the data in the VEH table. However, whenever I open the nested form to view the data, sometimes data is shown that doesn't belong. I check the ORG table, and names have been moved around or duplicated under the wrong codes. This doesn't appear to be a problem with the macro or any of the queries. Is there something going on with Access 2007 I should be aware of? I used the same file under 2003 and never had an issue. Sorry for being so verbose, but wanted to get as much info as possible in to help you help me. Thanks. |
#5
|
|||
|
|||
Data Mysteriously Changes
Here's the SQL of the query for the form I use to view data:
SELECT [OrgCode to Org].Org, CBF.[REG NO], CBF.[VE TP MKE], CBF.[WK ORD NO], DateValue([1stofYr]+[RecDay]-1) AS [Rec Date], CBF.[TI REC], CBF.[OLD ETIC DATE], CBF.[OLD ETIC TIME], CBF.[NEW ETIC DATE], CBF.[NEW ETIC TIME], CBF.[SHOP STUS], CBF.REMARKS, Right([DT REC],3) AS RecDay, DateValue("1 January 2008") AS 1stofYr FROM CBF INNER JOIN [OrgCode to Org] ON CBF.[ASSGN ORG] = [OrgCode to Org].OrgCode WHERE (([OrgCode to Org].Org)"EXCESS") ORDER BY CBF.[ASSGN ORG]; The macro I run first runs a delete query on the VEH table, then a standard TransferText action to import new data. "Jeff Boyce" wrote: If you post the SQL statement, folks here might be able to offer their observations. Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... No, I have no relationships set. I have a join in the query that returns the NAME field from the ORG table where the ID fields are equal. It only returns data where both sides are equal. "Jeff Boyce" wrote: Do you have Cascading Updates and/or Cascading Deletes turned on for the table relationships? Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... I have a db set up to provide our customers with information pertaining to vehicle maintenance. I import data from another program into a table (VEH). The only customer information this table has is coded. I have a query that gathers data from this table and another table (ORG) giving cleartext customer names to facilitate easier customer interaction. the ORG table has the following fields: ID NAME j1 Johnson, Mark In my import macro, I run a delete query to empty the VEH table, then import from an ASCII file to repopulate the table. The macro only modifies the data in the VEH table. However, whenever I open the nested form to view the data, sometimes data is shown that doesn't belong. I check the ORG table, and names have been moved around or duplicated under the wrong codes. This doesn't appear to be a problem with the macro or any of the queries. Is there something going on with Access 2007 I should be aware of? I used the same file under 2003 and never had an issue. Sorry for being so verbose, but wanted to get as much info as possible in to help you help me. Thanks. |
#6
|
|||
|
|||
Data Mysteriously Changes
MORE INFO...
I have opened my db a few times today, without running my import macro. Each time, data had been rearranged, i.e., ID# BE contained the cleartext for another ID#. I made the corrections and closed the db. Opened it up later, and ID# BE referenced a different cleartext customer. This happens with different ID #s each time the db is opened. Now, each time the db is open, the form automatically displays, which means the below query is run. Is there any way this query could be altering individual fields in unrelated records? I'm no Access expert, but I've never had this happen in any other db I've developed. "rickw" wrote: Here's the SQL of the query for the form I use to view data: SELECT [OrgCode to Org].Org, CBF.[REG NO], CBF.[VE TP MKE], CBF.[WK ORD NO], DateValue([1stofYr]+[RecDay]-1) AS [Rec Date], CBF.[TI REC], CBF.[OLD ETIC DATE], CBF.[OLD ETIC TIME], CBF.[NEW ETIC DATE], CBF.[NEW ETIC TIME], CBF.[SHOP STUS], CBF.REMARKS, Right([DT REC],3) AS RecDay, DateValue("1 January 2008") AS 1stofYr FROM CBF INNER JOIN [OrgCode to Org] ON CBF.[ASSGN ORG] = [OrgCode to Org].OrgCode WHERE (([OrgCode to Org].Org)"EXCESS") ORDER BY CBF.[ASSGN ORG]; The macro I run first runs a delete query on the VEH table, then a standard TransferText action to import new data. "Jeff Boyce" wrote: If you post the SQL statement, folks here might be able to offer their observations. Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... No, I have no relationships set. I have a join in the query that returns the NAME field from the ORG table where the ID fields are equal. It only returns data where both sides are equal. "Jeff Boyce" wrote: Do you have Cascading Updates and/or Cascading Deletes turned on for the table relationships? Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... I have a db set up to provide our customers with information pertaining to vehicle maintenance. I import data from another program into a table (VEH). The only customer information this table has is coded. I have a query that gathers data from this table and another table (ORG) giving cleartext customer names to facilitate easier customer interaction. the ORG table has the following fields: ID NAME j1 Johnson, Mark In my import macro, I run a delete query to empty the VEH table, then import from an ASCII file to repopulate the table. The macro only modifies the data in the VEH table. However, whenever I open the nested form to view the data, sometimes data is shown that doesn't belong. I check the ORG table, and names have been moved around or duplicated under the wrong codes. This doesn't appear to be a problem with the macro or any of the queries. Is there something going on with Access 2007 I should be aware of? I used the same file under 2003 and never had an issue. Sorry for being so verbose, but wanted to get as much info as possible in to help you help me. Thanks. |
#7
|
|||
|
|||
Data Mysteriously Changes
The query is a SELECT and therefore can not be changing the data. If
somethings actually changing the data in the table, it's not a Select query. Is either the CBF.[ASSGN ORG] or [OrgCode to Org].OrgCode fields the primary key field for their table? At least one should be. If you can't make at least one of them the PK, then there's a chance that you have a Many to Many relationship which can cause data anomolies. If data is missing, try changing the INNER JOIN to a Left or Right join. If there's not a matching record in both tables, the record won't return with an Inner Join. Have you made a backup copy of the database then do a Compact and Repair? That could see if there's a corruption problem. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "rickw" wrote: MORE INFO... I have opened my db a few times today, without running my import macro. Each time, data had been rearranged, i.e., ID# BE contained the cleartext for another ID#. I made the corrections and closed the db. Opened it up later, and ID# BE referenced a different cleartext customer. This happens with different ID #s each time the db is opened. Now, each time the db is open, the form automatically displays, which means the below query is run. Is there any way this query could be altering individual fields in unrelated records? I'm no Access expert, but I've never had this happen in any other db I've developed. "rickw" wrote: Here's the SQL of the query for the form I use to view data: SELECT [OrgCode to Org].Org, CBF.[REG NO], CBF.[VE TP MKE], CBF.[WK ORD NO], DateValue([1stofYr]+[RecDay]-1) AS [Rec Date], CBF.[TI REC], CBF.[OLD ETIC DATE], CBF.[OLD ETIC TIME], CBF.[NEW ETIC DATE], CBF.[NEW ETIC TIME], CBF.[SHOP STUS], CBF.REMARKS, Right([DT REC],3) AS RecDay, DateValue("1 January 2008") AS 1stofYr FROM CBF INNER JOIN [OrgCode to Org] ON CBF.[ASSGN ORG] = [OrgCode to Org].OrgCode WHERE (([OrgCode to Org].Org)"EXCESS") ORDER BY CBF.[ASSGN ORG]; The macro I run first runs a delete query on the VEH table, then a standard TransferText action to import new data. "Jeff Boyce" wrote: If you post the SQL statement, folks here might be able to offer their observations. Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... No, I have no relationships set. I have a join in the query that returns the NAME field from the ORG table where the ID fields are equal. It only returns data where both sides are equal. "Jeff Boyce" wrote: Do you have Cascading Updates and/or Cascading Deletes turned on for the table relationships? Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... I have a db set up to provide our customers with information pertaining to vehicle maintenance. I import data from another program into a table (VEH). The only customer information this table has is coded. I have a query that gathers data from this table and another table (ORG) giving cleartext customer names to facilitate easier customer interaction. the ORG table has the following fields: ID NAME j1 Johnson, Mark In my import macro, I run a delete query to empty the VEH table, then import from an ASCII file to repopulate the table. The macro only modifies the data in the VEH table. However, whenever I open the nested form to view the data, sometimes data is shown that doesn't belong. I check the ORG table, and names have been moved around or duplicated under the wrong codes. This doesn't appear to be a problem with the macro or any of the queries. Is there something going on with Access 2007 I should be aware of? I used the same file under 2003 and never had an issue. Sorry for being so verbose, but wanted to get as much info as possible in to help you help me. Thanks. |
#8
|
|||
|
|||
Data Mysteriously Changes
The [OrgCode to Org].OrgCode field is a primary key. When I return the
records, I want all records from CBF and only those which match from [OrgCode to Org]. That should be why Access designated an Inner Join. I didn't think the Select query would alter my data, but something strange is definitely happening. If it's not Access or my db, then I will need to get with our network folks and see if one (or more) of their screwy security measures is causing it. Of course, that's why I came here first...I doubt our network/computer support folks have enough knowledge to help figure this out. Rick "Jerry Whittle" wrote: The query is a SELECT and therefore can not be changing the data. If somethings actually changing the data in the table, it's not a Select query. Is either the CBF.[ASSGN ORG] or [OrgCode to Org].OrgCode fields the primary key field for their table? At least one should be. If you can't make at least one of them the PK, then there's a chance that you have a Many to Many relationship which can cause data anomolies. If data is missing, try changing the INNER JOIN to a Left or Right join. If there's not a matching record in both tables, the record won't return with an Inner Join. Have you made a backup copy of the database then do a Compact and Repair? That could see if there's a corruption problem. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "rickw" wrote: MORE INFO... I have opened my db a few times today, without running my import macro. Each time, data had been rearranged, i.e., ID# BE contained the cleartext for another ID#. I made the corrections and closed the db. Opened it up later, and ID# BE referenced a different cleartext customer. This happens with different ID #s each time the db is opened. Now, each time the db is open, the form automatically displays, which means the below query is run. Is there any way this query could be altering individual fields in unrelated records? I'm no Access expert, but I've never had this happen in any other db I've developed. "rickw" wrote: Here's the SQL of the query for the form I use to view data: SELECT [OrgCode to Org].Org, CBF.[REG NO], CBF.[VE TP MKE], CBF.[WK ORD NO], DateValue([1stofYr]+[RecDay]-1) AS [Rec Date], CBF.[TI REC], CBF.[OLD ETIC DATE], CBF.[OLD ETIC TIME], CBF.[NEW ETIC DATE], CBF.[NEW ETIC TIME], CBF.[SHOP STUS], CBF.REMARKS, Right([DT REC],3) AS RecDay, DateValue("1 January 2008") AS 1stofYr FROM CBF INNER JOIN [OrgCode to Org] ON CBF.[ASSGN ORG] = [OrgCode to Org].OrgCode WHERE (([OrgCode to Org].Org)"EXCESS") ORDER BY CBF.[ASSGN ORG]; The macro I run first runs a delete query on the VEH table, then a standard TransferText action to import new data. "Jeff Boyce" wrote: If you post the SQL statement, folks here might be able to offer their observations. Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... No, I have no relationships set. I have a join in the query that returns the NAME field from the ORG table where the ID fields are equal. It only returns data where both sides are equal. "Jeff Boyce" wrote: Do you have Cascading Updates and/or Cascading Deletes turned on for the table relationships? Regards Jeff Boyce Microsoft Office/Access MVP "rickw" wrote in message ... I have a db set up to provide our customers with information pertaining to vehicle maintenance. I import data from another program into a table (VEH). The only customer information this table has is coded. I have a query that gathers data from this table and another table (ORG) giving cleartext customer names to facilitate easier customer interaction. the ORG table has the following fields: ID NAME j1 Johnson, Mark In my import macro, I run a delete query to empty the VEH table, then import from an ASCII file to repopulate the table. The macro only modifies the data in the VEH table. However, whenever I open the nested form to view the data, sometimes data is shown that doesn't belong. I check the ORG table, and names have been moved around or duplicated under the wrong codes. This doesn't appear to be a problem with the macro or any of the queries. Is there something going on with Access 2007 I should be aware of? I used the same file under 2003 and never had an issue. Sorry for being so verbose, but wanted to get as much info as possible in to help you help me. Thanks. |
Thread Tools | |
Display Modes | |
|
|