How to find differences between datasets in a table

[rdemyan via AccessMonster.com]

I have to import building information from another system. I've decided to
keep track of each set of data downloaded by including a Download_Date. That
way I keep historical data.

My table has 25 fields in addition to Download_Date.

My question. I'd like the user to be able to find differences between
downloads. The primary key on the table is Building_ID and Download_Date.

I'd like a user to be able to select two values for Download_Date on a form
and then have the records based on Building_ID show up in a listbox if there
are any differences. So I'd probably have two listboxes, one showing the
record for the newer Download_Date and one for the older Download_Date.

So how do I code this. I need to be able to find differences in any field.
If different values for any field for the same Building_ID are found between
the download_dates, then display that record in one of the listboxes.
Also, what if a new Building_ID shows up or one disappears.
Ideas are appreciated.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200607/1

[Bob Quintal]

"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:63a50096a3240@uwe:

I have to import building information from another system.
I've decided to keep track of each set of data downloaded by
including a Download_Date. That way I keep historical data.

My table has 25 fields in addition to Download_Date.

My question. I'd like the user to be able to find differences
between downloads. The primary key on the table is
Building_ID and Download_Date.

I'd like a user to be able to select two values for
Download_Date on a form and then have the records based on
Building_ID show up in a listbox if there are any differences.
So I'd probably have two listboxes, one showing the record
for the newer Download_Date and one for the older
Download_Date.

So how do I code this. I need to be able to find differences
in any field. If different values for any field for the same
Building_ID are found between the download_dates, then display
that record in one of the listboxes. Also, what if a new
Building_ID shows up or one disappears.
Ideas are appreciated.

I'd suggest a large textbox instead of listboxes, because of
sync issues. I'd have a combobox or listbox select one building
id, and two comboboxes, one for each date.

I'd code a routine that opens 2 datasets, one for the first date
and one for the second, filtered for 1 building ID. Then I'd
loop through the fields collection of the recordsets, and since
they are from the same table, each field will be in the same
position, making comparison easy

something like (air code)
if nz(rs1.fields(idx),"Blank") _
<nz(rs2.fields(idx)," Blank" then
me.comp = me.comp _
& rs1.fields(idx).name _
& " was " & rs1.fields(idx).value _
& " Changed to " & rs2.fields(idx).value
& vbnewline
end if

this will add a row to the textbox for each field that's
different.

It should handle missing and added Id's just fine.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[rdemyan via AccessMonster.com]

Thanks, Bob.

I've taken your suggestion and implemented it. However, my code cycles
through all Building_IDs and not just one at a time.

So, the textbox text is getting pretty large and unreadable. I'd like to
print it to a report. How do I do that, as I'm concerned with the length of
the text.

Thanks.

Bob Quintal wrote:

>I have to import building information from another system.
I've decided to keep track of each set of data downloaded by

[quoted text clipped - 20 lines]

>>
Ideas are appreciated.

I'd suggest a large textbox instead of listboxes, because of
sync issues. I'd have a combobox or listbox select one building
id, and two comboboxes, one for each date.

I'd code a routine that opens 2 datasets, one for the first date
and one for the second, filtered for 1 building ID. Then I'd
loop through the fields collection of the recordsets, and since
they are from the same table, each field will be in the same
position, making comparison easy

something like (air code)
if nz(rs1.fields(idx),"Blank") _
<nz(rs2.fields(idx)," Blank" then
me.comp = me.comp _
& rs1.fields(idx).name _
& " was " & rs1.fields(idx).value _
& " Changed to " & rs2.fields(idx).value
& vbnewline
end if

this will add a row to the textbox for each field that's
different.

It should handle missing and added Id's just fine.

--
Message posted via http://www.accessmonster.com

[rdemyan via AccessMonster.com]

Actually, I guess what I could do is to write each line added to the textbox
to a table with a single field type equal to Memo. That way I can easily
print directly from the table.

rdemyan wrote:

>Thanks, Bob.

I've taken your suggestion and implemented it. However, my code cycles
through all Building_IDs and not just one at a time.

So, the textbox text is getting pretty large and unreadable. I'd like to
print it to a report. How do I do that, as I'm concerned with the length of
the text.

Thanks.

>>I have to import building information from another system.
I've decided to keep track of each set of data downloaded by

[quoted text clipped - 26 lines]

>>
It should handle missing and added Id's just fine.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200607/1

[Bob Quintal]

"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:63b1c6db46d94@uwe:

Thanks, Bob.

I've taken your suggestion and implemented it. However, my
code cycles through all Building_IDs and not just one at a
time.

So, the textbox text is getting pretty large and unreadable.
I'd like to print it to a report. How do I do that, as I'm
concerned with the length of the text.

Thanks.

Do it the same as a form. However, if I were designing the app,
I'd still use the one textbox per row. If you put the code in a
module as a user defined function that returns the string, you
could use that functrion in a query to bring up a continuous
rows form/report..

You would need to create a query that returns the unique
building IDs plus the udf and use that as the recordsource for
your form/report.

Bob Quintal wrote:

>>I have to import building information from another system.
I've decided to keep track of each set of data downloaded by

[quoted text clipped - 20 lines]

>>>
Ideas are appreciated.

I'd suggest a large textbox instead of listboxes, because of
sync issues. I'd have a combobox or listbox select one
building id, and two comboboxes, one for each date.

I'd code a routine that opens 2 datasets, one for the first
date and one for the second, filtered for 1 building ID. Then
I'd loop through the fields collection of the recordsets, and
since they are from the same table, each field will be in the
same position, making comparison easy

something like (air code)
if nz(rs1.fields(idx),"Blank") _
<nz(rs2.fields(idx)," Blank" then
me.comp = me.comp _
& rs1.fields(idx).name _
& " was " & rs1.fields(idx).value _
& " Changed to " & rs2.fields(idx).value
& vbnewline
end if

this will add a row to the textbox for each field that's
different.

It should handle missing and added Id's just fine.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com