help writing a multiple table query

[mbatestblrock]

I really need some direction writing a query here.

I provided a screen cap so you may catch the drift.

http://www.mykesdesigns.com/tblrock1/access/terminfo.jpg

as you may see there are a ton of tables. there are 58 tables that are all nearly identical to the one shown...

each table represents a txt file that is imported every night to our server and each txt file is a store. These files hold the info about the terminals..

Every day I sift through these same 60 files and look to make sure #2 in the TermName field is set to Backup in the Role field, and also that no other Terms have the Role backup in the field.

I would really love some direction to take on how to make a query output a "REDFLAG" if you will, pointing out that a sertain table is not how it should be.


I hope this makes sense. Ill explain anything better that anyone wants me to.

Thanks a ton in advance!

[nico5038]

It would be best to create a function that processes all tables and performs the checks needed. Are you able/willing to use VBA code ?
Is the Backup-role test all that's needed, or do you use the data for more ?

Nic;o)

[mbatestblrock]

It would be best to create a function that processes all tables and performs the checks needed. Are you able/willing to use VBA code ?
Is the Backup-role test all that's needed, or do you use the data for more ?

Nic;o)

heyy I would be willing to whatever is necessary, the database is only being used for this function.

Sometimes these locations have two terminals set as backup, and sometimes they dont have a backup.

what I need it to be able to run it and have it return anything that is not how it should be so I can email them and tell them to fix it. whether that be they have two terminals on back up or none on backup.

Ill even post this database on the internet if that helps...

Please get back to me!
thank you!

[nico5038]

No need, the function isn't that hard.

function fncFindBackup()
' these dim's will need you to set a reference to the "Microsoft DAO version 3.##" library
' and remove the "Microsoft Active Data Objects" library when selected.
' use Tools/References to (un)set them
dim td as DAO.Tabledef
dim rs as DAO.Recordset
' The results are stored in a temp table tblWarnings
' Create that table with one 250 char text field "Warning"
dim rsF as DAO.recordset 

' initialize the warnings
currentdb.execute ("delete * from tblWarning")

set rsF = currentdb.openrecordset("tblWarnings")

' Process all tables
for each td in currentdb.tabledefs
   ' check the name to "fit"
   if left(td.name,8) = "terminfo" then
      ' get all records for 'Backup' role
      set rs = currentdb.openrecordset(select * from " & td.name & " where Role ='Backup'")
          if rs.eof and rs.bof then
             ' Nothing found
             rsF.addnew
             rsF!Warnings = "Table: " & td.name & " has no backup"
             rsF.Update
          else
             ' move to end for getting correct count
             rs.movelast
             ' test one record
             if rs.recordcount > 1 then
                rsF.addnew
                rsF!Warnings = "Table: " & td.name & " has " & rs.recordcount & " backups"
                rsF.Update
              else
                 'one record found, now test "02=" in start of [Terminal Info] field
                 if left(rs![Terminal Info],3) <> "02=" then
                   rsF.addnew
                   rsF!Warnings = "Table: " & td.name & " has no backup in '02='"
                   rsF.Update
                 endif 
             endif   
          endif
   endif
next

end function

Just read the comment to see what's happening. The code needs to be copy/pasted into a module (E.g. "modWarning s")

Getting the idea ?

Nic;o)

[mbatestblrock]

EDIT- 3:48
I can safely say that I am CONFUSED....

I tried doing that..http://www.mykesdesigns.com/tblrock1/access/module.jpg

I dont know if I know what exactly what to do, but I hit run and then get this?

http://www.mykesdesigns.com/tblrock1/access/module2.jpg

Please forgive me for being ridiculously dumb, I am trying to read up on it but time is NOT of the essence and I thank you very much!

No need, the function isn't that hard.

[code=vb]
function fncFindBackup()
' these dim's will need you to set a reference to the "Microsoft DAO version 3.##" library
' and remove the "Microsoft Active Data Objects" library when selected.
' use Tools/References to (un)set them
dim td as DAO.Tabledef
dim rs as DAO.Recordset
' The results are stored in a temp table tblWarnings
' Create that table with one 250 char text field "Warning"
dim rsF as DAO.recordset

' initialize the warnings
currentdb.execu te ("delete * from tblWarning")

set rsF = currentdb.openr ecordset("tblWa rnings")

' Process all tables
for each td in currentdb.table defs
' check the name to "fit"
if left(td.name,8) = "terminfo" then
' get all records for 'Backup' role
set rs = currentdb.openr ecordset(select * from " & td.name & " where Role ='Backup'")
if rs.eof and rs.bof then
' Nothing found
rsF.addnew
rsF!Warnings = "Table: " & td.name & " has no backup"
rsF.Update
else
' move to end for getting correct count
rs.movelast
' test one record
if rs.recordcount > 1 then
rsF.addnew
rsF!Warnings = "Table: " & td.name & " has " & rs.recordcount & " backups"
rsF.Update
else
'one record found, now test "02=" in start of [Terminal Info] field
if left(rs![Terminal Info],3) <> "02=" then
rsF.addnew
rsF!Warnings = "Table: " & td.name & " has no backup in '02='"
rsF.Update
endif
endif
endif
endif
next

end function
[/code]

Just read the comment to see what's happening. The code needs to be copy/pasted into a module (E.g. "modWarning s")

Getting the idea ?

Nic;o)

[nico5038]

Guess the error was related to the linenumbers that shouldn't be copied.
Thus I've removed the "code-tags" in my previous comment so you have the plain text needed.

After saving the function place the textcursor on a command and press F8 to start excuting the first line. Then press F8 again for the next line, etc.
That way you can see what how code works and give me the line and message when you run into an error.

Nic;o)

[mbatestblrock]

I hope I am doing all of this correctly!


http://www.mykesdesign s.com/tblrock1/access/module3.jpg

[nico5038]

Sorry, forgot a " before the SELECT, try:

Set rs = CurrentDb.OpenR ecordset("selec t * from " & td.Name & " where Role ='Backup'")

Nic;o)

[mbatestblrock]

OKay so now I got two errors telling me to create tblwarning and tblwarnings

I did that... and now I run the code. and nothing happens??

I dont have any errors though!

[nico5038]

Oops, typo, use:
currentdb.execu te ("delete * from tblWarnings")
and make sure only a tblWarnings is in your database.

Now use the F8 to "walk" through the code to detect where errors are found. Each error should be found in the tblWarnings, but when all's OK it will be empty...

Nic;o)

[mbatestblrock]

okay, so I fixed there is only a tblwarnings table now. and f8 through the code and all is well. I hit the run button, and it doesnt take any time at all, almost seemingly as if nothing is going on..

and in the tblWarnings table there is nothing in there.. there should be a few errors though....

again thank you!!! I cant even believe the support on this board!

[nico5038]

OK, let's see where it goes wrong.
The F8 is really needed multiple times to step line by line through the code.
Don't use the Run (F5) as it will make the progress "invisible" as it executes all code instantly.
Place the textcursor on a command and press F8 to see the yellow line work through the code. When you hover the mousepointer above a field the value will show, check or it's the value you expect.
Especially check or the tablenames are found, else nothing will happen...

Nic;o)

[mbatestblrock]

I can press the f8 key 7 times. then it kicks back up to
If Left(td.Name, 8) = "terminfo" Then

then
end if
next


I can hover the codes but it doesn't make too much sense to me..

Its getting so close to being done too!

[nico5038]

Looks to me no table with "terminfo" as the first 8 characters is found.
Hover above the td.name to see which tablename is "active" and check the "terminfo" to be exactly correct.

Nic;o)

[mbatestblrock]

td.Name="~TMPCL P340661"
???

that is when I hover over td.Name

My tables are linked tables to a txt file, not sure if that has anything to do with it