Need to identify all edits

[Guest]

Hi there. Using A02 on XP. Not a programmer but love learning stuff here.

1. I have a DB that I use to import a .txt file from a system we have and
it names the table for the contract number in a field [RunThisOne]&"Copy".
An AutoNum field is added on import.

2. I then export to an .xls file some fields with employee data including
the AutoNum field as [ID] but also export a field [SSN] with zeros in place
of the Social Security Numbers that are in the imported table (for privacy
purposes).

3. The client edits the file by changing DOB, DOH, LName, etc. They then
add new employees (including SSN's so we can set them up) and any data
available. The client then returns the file to us.

4. Next I import the .xls file back into Access and name it
[RunThisOne]&"Revised".

5. The final step is to run an update query on [RunThisOne]&"Revised" to
bring the SSN's back in and then export the Revised table to a comma
delimited .csv file that will be loaded back into the system from whence it
came.

What I have to do now is insert a step between 4 and 5 where I run a report
that shows which fields were edited from [RunThisOne] & "Copy" and
[RunThisOne] & "Revised". I know how to create a formula in Excel that
says: If FileName1.CellA1 equals FileName2.CellA1, "OK", "Edited" but wonder
what would be the best way to do it in Access. Or I could show the Revised
data but show it RED if changed, otherwise show it BLACK. I'm open to
suggestions. Want to go at it right rather than fight with an ugly
alternative.

Any advice would be appreciated. Thanks for your time!

 

[Guest]

Hi, Bonnie.

wonder
what would be the best way to do it in Access.

Use a query that compares what is and isn't in each of the tables. This
would be a full outer join for other database engines, but Jet isn't capable
of full OUTER JOIN's yet. The workaround is a UNION query containing a RIGHT
OUTER JOIN query and a LEFT OUTER JOIN query. For an example of the
requirement (a primary key or unique index that is consistent between the two
tables), syntax, and how to read the resulting query, please see the
following Web page:

http://groups.google.com/group/micr...066d2/26ae57ef3c837505?hl=en#26ae57ef3c837505

Create a report based upon this UNION query, and you can color code the font
in the text boxes when the new table doesn't match the old table for any
particular record's column.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi there. Using A02 on XP. Not a programmer but love learning stuff here.

1. I have a DB that I use to import a .txt file from a system we have and
it names the table for the contract number in a field [RunThisOne]&"Copy".
An AutoNum field is added on import.

2. I then export to an .xls file some fields with employee data including
the AutoNum field as [ID] but also export a field [SSN] with zeros in place
of the Social Security Numbers that are in the imported table (for privacy
purposes).

3. The client edits the file by changing DOB, DOH, LName, etc. They then
add new employees (including SSN's so we can set them up) and any data
available. The client then returns the file to us.

4. Next I import the .xls file back into Access and name it
[RunThisOne]&"Revised".

5. The final step is to run an update query on [RunThisOne]&"Revised" to
bring the SSN's back in and then export the Revised table to a comma
delimited .csv file that will be loaded back into the system from whence it
came.

What I have to do now is insert a step between 4 and 5 where I run a report
that shows which fields were edited from [RunThisOne] & "Copy" and
[RunThisOne] & "Revised". I know how to create a formula in Excel that
says: If FileName1.CellA1 equals FileName2.CellA1, "OK", "Edited" but wonder
what would be the best way to do it in Access. Or I could show the Revised
data but show it RED if changed, otherwise show it BLACK. I'm open to
suggestions. Want to go at it right rather than fight with an ugly
alternative.

Any advice would be appreciated. Thanks for your time!

 

[John Vinson]

Hi there. Using A02 on XP. Not a programmer but love learning stuff here.

1. I have a DB that I use to import a .txt file from a system we have and
it names the table for the contract number in a field [RunThisOne]&"Copy".
An AutoNum field is added on import.

OUCH.

Storing data in a tablename is VERY BAD DESIGN. Are these tables
really enormous? i.e. would it not be better to have *one* table with
all of the text files, with the contract number as a data field within
the table?

2. I then export to an .xls file some fields with employee data including
the AutoNum field as [ID] but also export a field [SSN] with zeros in place
of the Social Security Numbers that are in the imported table (for privacy
purposes).
3. The client edits the file by changing DOB, DOH, LName, etc. They then
add new employees (including SSN's so we can set them up) and any data
available. The client then returns the file to us.

4. Next I import the .xls file back into Access and name it
[RunThisOne]&"Revised".

You could also just link to the spreadsheet rather than importing it.

5. The final step is to run an update query on [RunThisOne]&"Revised" to
bring the SSN's back in and then export the Revised table to a comma
delimited .csv file that will be loaded back into the system from whence it
came.

And again, you don't need a Table to export. You could just export
from a Query joining the copy and the revised tables.

What I have to do now is insert a step between 4 and 5 where I run a report
that shows which fields were edited from [RunThisOne] & "Copy" and
[RunThisOne] & "Revised". I know how to create a formula in Excel that
says: If FileName1.CellA1 equals FileName2.CellA1, "OK", "Edited" but wonder
what would be the best way to do it in Access. Or I could show the Revised
data but show it RED if changed, otherwise show it BLACK. I'm open to
suggestions. Want to go at it right rather than fight with an ugly
alternative.

A Query joining the two tables on ID with a Criterion on
Filename2.Fieldname of

<> Filename1.Fieldname

will return only those records where the field has changed; or you
could put a calculated field in the Query:

Changed: IIF([Filename1].[Fieldname] = [Filename2].[Fieldname],
"Unchanged", "Changed")

But I fear you're taking a very "spreadsheety" design approach - you
may want to step back and study up a bit on Queries, which can do a
lot of what you're doing with redundant tables!

John W. Vinson[MVP]