Help with Query Checking 2 Fields

[Alan]

Hi,

I'm hoping that someone will be able to help me with a problem I'm having
with queries.

At the moment I have 3 tables as follows:

Table 1:
ID
MasterList:
Overall Status:

Table 2:
Machine
Status1

Table 3
Machine
Status1

My Query lists all machines in the Master List and then Status 1 from both
table 2 and 3. Thefore I get results as follows:

Machine: Table2.Status1 Table3.Status1
A OK Unknown
B Unknown OK
C OK OK
D Unknown Unknown

What I want to be able to do is have the query check whether either
Table2.Status1 has OK or Table3.Status1 has OK for each of the machines and
if either of them do contain ok then to put OK into the overall status in
table1. Therefore I would get the following result:


Machine: Table2.Status1 Table3.Status1 Table1.Overall
Status
A OK Unknown
OK
B Unknown OK
OK
C OK OK
OK
D Unknown Unknown
Unknown

Is this possible???

Thanks in advance for any help

Regards
Alan

 

[Jeff Boyce]

Alan

Based on the description in your post, you have two tables with identical
structure (Table2 and Table3). I am assuming that your [Machine] fields in
each of these are actually "ID" fields, and that you are using them to refer
back to your master table (Table1).

Since I not there, I don't understand the reasons you've (apparently)
created duplicate structures. Generally, doing this isn't necessary (and
has some downsides) in a well-normalized relational database (i.e., Access).

Could you describe a bit more about your actual data? For example, if you
have a machine row in both Table2 and Table3, how do you know which
[Status1] is the "correct" one? Are there other fields you didn't mention?
Are your tables actually named "1", "2" and "3"?

 

[Alan]

Hi Jeff,

Ok, my apologies for any confusion. I'll explain a bit more about what we
have.....

We have list of machines that we scan in 2 different ways. The first scan
is done by system 1 when machines logon and the second scan is performed by
network discovery.

What we end up with is 2 reports, 1 from the logon scan and the 2nd from the
discovery. These reports contain the same fields and the 2 reports can
contain the same machines, but there are some machines that appear just in
the discovery report and some that appear just on the logon report as
machines come and go, especially laptop users.

What we are trying to achive is to have a report that gives a list of all
the unique machines, which we have a master list, and then overall whether
they have a particular patch installed or software application installed.
So if the logon scan picks up that a particular patch is installed and the
network discovery does not, because the machine is not there when it scans,
the overall result would be shown as the patch is installed.

We have an Excel document that does loads of VLookups at the moment with
this data but this takes ages to calculate and I thought that access might
be the better solution to try.

Our tables are named Network and Logon and we also have a table called
Master which has the master list of machines. The network and logon tables
contain the same fields, ID, MachineName, Operating_System, ServicePack,
StatusofPatch1, StatusofPatch2....etc..

The master table just contains the machine names.

Hope this helps explain it a bit more???

Regards
Alan

Alan

Based on the description in your post, you have two tables with identical
structure (Table2 and Table3). I am assuming that your [Machine] fields
in
each of these are actually "ID" fields, and that you are using them to
refer
back to your master table (Table1).

Since I not there, I don't understand the reasons you've (apparently)
created duplicate structures. Generally, doing this isn't necessary (and
has some downsides) in a well-normalized relational database (i.e.,
Access).

Could you describe a bit more about your actual data? For example, if you
have a machine row in both Table2 and Table3, how do you know which
[Status1] is the "correct" one? Are there other fields you didn't
mention?
Are your tables actually named "1", "2" and "3"?

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

Hi,

I'm hoping that someone will be able to help me with a problem I'm having
with queries.

At the moment I have 3 tables as follows:

Table 1:
ID
MasterList:
Overall Status:

Table 2:
Machine
Status1

Table 3
Machine
Status1

My Query lists all machines in the Master List and then Status 1 from
both
table 2 and 3. Thefore I get results as follows:

Machine: Table2.Status1 Table3.Status1
A OK Unknown
B Unknown OK
C OK OK
D Unknown Unknown

What I want to be able to do is have the query check whether either
Table2.Status1 has OK or Table3.Status1 has OK for each of the machines and
if either of them do contain ok then to put OK into the overall status in
table1. Therefore I would get the following result:


Machine: Table2.Status1 Table3.Status1
Table1.Overall
Status
A OK Unknown
OK
B Unknown OK
OK
C OK OK
OK
D Unknown Unknown
Unknown

Is this possible???

Thanks in advance for any help

Regards
Alan

 

[Jeff Boyce]

Alan

Please see my comments in-line below...

Hi Jeff,

Ok, my apologies for any confusion. I'll explain a bit more about what we
have.....

We have list of machines that we scan in 2 different ways. The first scan
is done by system 1 when machines logon and the second scan is performed by
network discovery.

What we end up with is 2 reports, 1 from the logon scan and the 2nd from the
discovery. These reports contain the same fields and the 2 reports can
contain the same machines, but there are some machines that appear just in
the discovery report and some that appear just on the logon report as
machines come and go, especially laptop users.

If you have two sets of data, identical except for the "source", in Access
you only need one table, with a field for the "source". I am assuming that
what you call "two reports" means two sets of data.

What we are trying to achive is to have a report that gives a list of all
the unique machines, which we have a master list, and then overall whether
they have a particular patch installed or software application installed.

Are you saying you have a master table that lists all possible machines?

So if the logon scan picks up that a particular patch is installed and the
network discovery does not, because the machine is not there when it scans,
the overall result would be shown as the patch is installed.

You want to know if machine 99 has patch 33 installed, right?

We have an Excel document that does loads of VLookups at the moment with
this data but this takes ages to calculate and I thought that access might
be the better solution to try.

Our tables are named Network and Logon and we also have a table called
Master which has the master list of machines. The network and logon tables
contain the same fields, ID, MachineName, Operating_System, ServicePack,
StatusofPatch1, StatusofPatch2....etc..

Oh boy, step away from the keyboard! In Excel, you are pretty much limited
to using "repeating fields" ("StatusofPatch1", "StatusofPatch2", ...
"StatusofPatchn"). In Excel, every time you add a new patch, you add a new
column.

In Access (a relational database), this design for your data (table) will
cause both you and Access serious headaches. If every time a new patch is
added you have to add a new column to the (Access) table, you will also have
to add the new field to any/all queries related to patches, and modify
any/all forms and reports related to patches, and revisit/revise any/all
code that includes/involves patches ... a real maintenance nightmare.

A more normalized way to portray the data is something like as follows
(remember, you're there, I'm not, so your needs/situation may differ):

tblMachineScan
MachineScanID (a primary key)
MachineID (?the ID from your master table)
PatchID (an ID from an independent table listing patches)
ScanDate
PatchStatus
PatchStatusSource (this would be your "logon" vs. "discovery")

and

tlkpPatch
PatchID (a primary key)
PatchTitle
PatchDescription
DatePatchAvailable

With a data design like this, you can query your MachineScan table to find
all patches on any specific Machine.

Or am I still not visualizing your situation?