Questions about exporting records based on "status"

[Guest]

I have an "issues" application where tblIssues is essentially the main table.
The table is populated using frmIssues, which is based on a query which
allows me to display (in the form) certain values in drop down boxes by word
instead of the numerical values (autonumbers) that go in the table).

One of the fields is "status" which can, among other values, be "closed".
My questions relate to the records in the tblIssues table:

 

[Guest]

Append to above:

1. Can I write a query (or use some other tool) that will export the
records with a given status (closed) to a seperate database file (or,
specifically, a table therein) on: A. A given/regular time period or B.
Whenever it's manually run.

2. Would it be possible to, while benefiting from keeping the tblIssues
records down in number by moving the closed ones to another db, still used
the closed ones in the other db in things like reports and/or queries in the
main db?

If there's a tutorial or a bit of code or something to help with either of
those, I'd be grateful for a link.

Thanks,
CW

PS: Original post got away from me somehow!

 

[Guest]

Not a good idea. This will only make your life harder. It appears you want
to minimize the size of your issuses table, but still use the historical
data. The idea sounds good for the current form, but to use the closed data
in reports once it has been moved to another table or mdb, is a lot more
work. I would suggest you just use a query for your form that is filter on
STATUS <> "closed"

 

[Guest]

I would suggest you just use a query for your form that is filter on
STATUS <> "closed"

Thanks for the reply.

I should have been more specific, but the query I alluded to in my original
post IS filter on Status '<>"closed"'. I think that will help in terms of
some things, but it doesn't address the long-term size concerns I'm facing.

Originally, there was no real size concerns....at least none that would
need addressing for a long time, but I had to add audit trail functionality
and suddenly instead of one entry per issue I could have several. I think a
safe guesstimate (for planning purposes) would be maybe 8 entries per issue
on average. That's based on two entries for the original issue (one in the
tblIssues and one in the audit table), and then 2 per edit thereafter. Also,
any deleted issues would still be represented by AT LEAST two entries in the
audit table.

There's only two ways I can think of to signicantly effect the size given
the parameters I've described (well, maybe three). One, systematically
remove all records from the main access db into some other access db. Two,
figure out if there's any way to move the audit table to another db. I don't
think you can do it, and even if you could this seems like it would only
slightly delay the size issue since much of the size issue is related to the
audit table itself. The possible third choice is to figure out how to
eliminate some of the audit trail entries. For example, an audit entry gets
made whenever you add a new issue to the database. That seems superflous
given the fact that you have the main entry already in the tblIssues and any
subsequent edits are going to record an "edit from" entry that will be a
copy of the orignal entry anyway.

I don't even know if it's that big of a concern, but it sure seems like the
data storage requirements went WAY up with the audit trail addition...

Oh well, have to think some more about it.......thanks for the reply.

CW

 

[Guest]

There's only two ways I can think of to signicantly effect the size given
the parameters I've described (well, maybe three). One, systematically
remove all records from the main access db into some other access db.

Not enough coffee today! The above should read ....systematically remove
all CLOSED records from....

CW

 

[Guest]

Is size really an issue? Number of records does not matter. If your database
is properly split, 2Gig is a lot of data. If the records are large and the
volume sufficient, then it is possible, but not likely you could hit that
limit.

Now as to your audit log. It definitly can be in another mdb. Just put all
the tables related to the audit log in another mdb, and link to it from your
front end database. If is not unusal to link to more than one mdb at the
same time.

 

[Guest]

Thanks again for responding.

Is size really an issue? Number of records does not matter. If your database
is properly split, 2Gig is a lot of data. If the records are large and the
volume sufficient, then it is possible, but not likely you could hit that
limit.

I'm not sure if the 2g limit is a problem or not. My reference to the
"number" of records was probably a clumsy way to point out that instead of
one record per issue, I now was looking at essentially 8 records per issue
which means I'm basically increasing the data stored per issue by 8 times.

Now as to your audit log. It definitly can be in another mdb. Just put all
the tables related to the audit log in another mdb, and link to it from your
front end database. If is not unusal to link to more than one mdb at the
same time.

I'll have to look more closely at this. It would be nice to put the audit
log in a seperate db. That would definitely help some. I used Allen
Browne's solution on his website, and he states in relevant part the
following:

"For each table you wish to audit, you will create two more tables, as
follows. If your database is split into two mdb files, the table created at
step 2 must be in the code mdb (workstation file), and the table at step 5 in
the data mdb (server file.)"

Now that I think about it, my reading of the above probably was more
literal than he intended. He may very well not have meant to preclude
putting the audit tables in a seperate db, just meant to inform the reader
which part of a split db needed access to which of the new tables. Maybe
I'll play around with that a little and see how it goes.

Thanks again. Always helps to soundboard off someone with some real
knowledge.

CW