[AccessD] Query Help

[Rocky Smolin]

I know there's probably a slicker way of doing this but brute force:

make copies of the tables so this next reformatting doesn't change the data
for everybody else.

reformat the DOS to something more conventional like 201012 --> 12/31/2010
or 31/12/2010, depending on where you live, using an update query:

update to Right(DOS,2) & "/" & "31" & "/" & Left(DOS,4) or "31" & "/" &
Right(DOS,2) & "/" & Left(DOS,4)

then you can get the count of all the records with a date GREATER than your
test date using DCOUNT (field name, table name, DOS *>* "#" & test date &
"#").

If the DCOUNT is 0 then that table has no dates after your target date.

Or if you need the number of records with DOS LESS than your test date date
using  DCOUNT (field name, table name, DOS *< *"#" & test date & "#").

If you have lots of tables then you have to do this process in a loop,
iterating through all the tables. Hopefully the tables have some sort of
way to identify them so you can set your loop to run For ALL TABLES with
Name Like "*xxxx*"

Top of my head.

r

On Thu, Nov 16, 2023 at 1:33 PM RANDALL R ANTHONY via AccessD <
accessd at databaseadvisors.com> wrote:

> Hello group,
> Long time no see, so to speak. Hey, I need to run a query across multiple
> tables to see when last used (I know, bear with me).  Each table has a
> DateofService column, DOS, and it's in a year month format, ie, 202301.  So
> I'd like to use a where clause like where DOS < 201012 and the results show
> that 20 out of 45 tables don't have a DOS date greater than that.  Looking
> for old data tables that can be deleted to free up space when the app is
> run.  Is this a no-brainer or am I missing something?
>
> TIA.
>
> Randall R. Anthony, MCP
> Database Administrator
>
> Disclaimer:
>
>
> This electronic message and its contents and attachments contain
> information from Sentara Health and is confidential or otherwise protected
> from disclosure. The information is intended to be for the addressee only.
>
> If you are not the addressee, any disclosure, copy, distribution or use of
> the contents of this message is prohibited. If you have received this
> electronic message in error, please notify us immediately and destroy the
> original message and all copies.
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>