If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with Query Checking 2 Fields
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 |
#2
|
|||
|
|||
Help with Query Checking 2 Fields
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 "Alan" alan@nospam wrote in message ... 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 |
#3
|
|||
|
|||
Help with Query Checking 2 Fields
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 "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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 "Alan" alan@nospam wrote in message ... 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 |
#4
|
|||
|
|||
Help with Query Checking 2 Fields
Alan
Please see my comments in-line below... "Alan" alan@nospam wrote in message ... 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? -- Regards Jeff Boyce Office/Access MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cross tab query construction with Subqueries | Steven Cheng | Running & Setting Up Queries | 7 | February 13th, 2006 06:52 PM |
improving performance by indexing query criteria fields | Paul James | General Discussion | 20 | February 16th, 2005 07:55 PM |
improving performance by indexing query criteria fields | Paul James | Running & Setting Up Queries | 20 | February 16th, 2005 07:55 PM |
Unmatched Query Mess | Natalia | Running & Setting Up Queries | 8 | October 28th, 2004 02:36 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM |