I can't wrap my head around this query

I have two tables.  One that has ComputerID and some other fields.  The
ComputerID field is unique.  I also have another table that has a non-unique
Computer ID field and a SWName  record for each software program installed
on that computer.  What I would like to do is find out what ComputerIDs DO
NOT have a specific program (ProgramA).  I was thinking I would get what I
needed if I could take the results for those ComputerIDs that DO have
ProgramA and compare that with all of the ComputerIDs possible and take the
difference.  Is there some way to do this?  If not, what would I have to add
to one, the other, or both tables to get what I need?

Thank you.

Kevin


0
Kevin
6/6/2007 2:57:37 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
904 Views

Similar Articles

[PageSpeed] 46

This will give you all the computers that have that software: (save as
queryA)
select
  ComputerID
from
  your_table_with_sw
where
  SWName = [Enter a prgram name]

This query will select all computers that don't exist in queryA
select
  ci.*
from
  your_table_with_computer_info AS ci
  left join queryA as qa on qa.ComputerID = ci.ComputerID
where
  qa.ComputerID is null

Cheers,
Jason Lepack

On Jun 6, 10:57 am, "Kevin" <noemailh...@there.net> wrote:
> I have two tables.  One that has ComputerID and some other fields.  The
> ComputerID field is unique.  I also have another table that has a non-unique
> Computer ID field and a SWName  record for each software program installed
> on that computer.  What I would like to do is find out what ComputerIDs DO
> NOT have a specific program (ProgramA).  I was thinking I would get what I
> needed if I could take the results for those ComputerIDs that DO have
> ProgramA and compare that with all of the ComputerIDs possible and take the
> difference.  Is there some way to do this?  If not, what would I have to add
> to one, the other, or both tables to get what I need?
>
> Thank you.
>
> Kevin


0
Jason
6/6/2007 3:12:52 PM
Kevin

The query wizard includes an "Unmatched" query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Kevin" <noemailhere@there.net> wrote in message 
news:Oa0yGsEqHHA.3660@TK2MSFTNGP04.phx.gbl...
>I have two tables.  One that has ComputerID and some other fields.  The
> ComputerID field is unique.  I also have another table that has a 
> non-unique
> Computer ID field and a SWName  record for each software program installed
> on that computer.  What I would like to do is find out what ComputerIDs DO
> NOT have a specific program (ProgramA).  I was thinking I would get what I
> needed if I could take the results for those ComputerIDs that DO have
> ProgramA and compare that with all of the ComputerIDs possible and take 
> the
> difference.  Is there some way to do this?  If not, what would I have to 
> add
> to one, the other, or both tables to get what I need?
>
> Thank you.
>
> Kevin
>
> 


0
Jeff
6/6/2007 3:14:26 PM
Jason,

You rock!  That worked like a charm.    Now, where can I learn how to do
that?  Most tutorials only cover the basics...

Thanks!!!

Kevin

"Jason Lepack" <jlepack@gmail.com> wrote in message
news:1181142772.500547.166160@q66g2000hsg.googlegroups.com...
> This will give you all the computers that have that software: (save as
> queryA)
> select
>   ComputerID
> from
>   your_table_with_sw
> where
>   SWName = [Enter a prgram name]
>
> This query will select all computers that don't exist in queryA
> select
>   ci.*
> from
>   your_table_with_computer_info AS ci
>   left join queryA as qa on qa.ComputerID = ci.ComputerID
> where
>   qa.ComputerID is null
>
> Cheers,
> Jason Lepack
>
> On Jun 6, 10:57 am, "Kevin" <noemailh...@there.net> wrote:
> > I have two tables.  One that has ComputerID and some other fields.  The
> > ComputerID field is unique.  I also have another table that has a
non-unique
> > Computer ID field and a SWName  record for each software program
installed
> > on that computer.  What I would like to do is find out what ComputerIDs
DO
> > NOT have a specific program (ProgramA).  I was thinking I would get what
I
> > needed if I could take the results for those ComputerIDs that DO have
> > ProgramA and compare that with all of the ComputerIDs possible and take
the
> > difference.  Is there some way to do this?  If not, what would I have to
add
> > to one, the other, or both tables to get what I need?
> >
> > Thank you.
> >
> > Kevin
>
>


0
Kevin
6/6/2007 7:11:13 PM
Almost everything that I know was learned through searching Google
Groups... If I couldn't find it, then I posted it in one of the
groups.

I read these ones regularly:
Microsoft.Public.Access.Queries
Comp.Databases.MsAccess
Microsoft.Public.Access
Microsoft.Public.Access.Tablesdbdesign

Cheers,
Jason Lepack



On Jun 6, 3:11 pm, "Kevin" <noemailh...@there.net> wrote:
> Jason,
>
> You rock!  That worked like a charm.    Now, where can I learn how to do
> that?  Most tutorials only cover the basics...
>
> Thanks!!!
>
> Kevin
>
> "Jason Lepack" <jlep...@gmail.com> wrote in message
>
> news:1181142772.500547.166160@q66g2000hsg.googlegroups.com...
>
>
>
> > This will give you all the computers that have that software: (save as
> > queryA)
> > select
> >   ComputerID
> > from
> >   your_table_with_sw
> > where
> >   SWName = [Enter a prgram name]
>
> > This query will select all computers that don't exist in queryA
> > select
> >   ci.*
> > from
> >   your_table_with_computer_info AS ci
> >   left join queryA as qa on qa.ComputerID = ci.ComputerID
> > where
> >   qa.ComputerID is null
>
> > Cheers,
> > Jason Lepack
>
> > On Jun 6, 10:57 am, "Kevin" <noemailh...@there.net> wrote:
> > > I have two tables.  One that has ComputerID and some other fields.  The
> > > ComputerID field is unique.  I also have another table that has a
> non-unique
> > > Computer ID field and a SWName  record for each software program
> installed
> > > on that computer.  What I would like to do is find out what ComputerIDs
> DO
> > > NOT have a specific program (ProgramA).  I was thinking I would get what
> I
> > > needed if I could take the results for those ComputerIDs that DO have
> > > ProgramA and compare that with all of the ComputerIDs possible and take
> the
> > > difference.  Is there some way to do this?  If not, what would I have to
> add
> > > to one, the other, or both tables to get what I need?
>
> > > Thank you.
>
> > > Kevin- Hide quoted text -
>
> - Show quoted text -


0
Jason
6/6/2007 8:29:23 PM
Reply:

Similar Artilces:

query update
I have a query that is derived from 3 tables with relationships. i want the user to open a form and be able to change data in that query. i'm assuming it is not letting me because it is derived from more than one table. is the only solution to this is to create a table off the query and then base my form on that table. wasnt sure if there was a different way around this. thanks DD wrote: > I have a query that is derived from 3 tables with relationships. i > want the user to open a form and be able to change data in that > query. i'm assuming it is not letting me ...

Query with two date field for Date range
How do I create a Query using two tables that have dates field to calculate a value using both the tables for a Date Range. For Example I have four TABLES- Time Card(TC), PROJECT, TC hours(HOURS), and TC expenses(EXPENSE). TC contains name of employees and their billing rates. PROJECT contains Name of Project. HOURS field are TCId, PROJECTId, Dateworked, and Hours. EXPENSE contains fields TCId, PROJECTId, Dateexpense, Expense. I want to build a query that over a Date Range that sums billable amount=hours worked* billing rate+ expense. I set the Dateworked >=[forms]![Report Date Range]![Be...

Word wrapping paragraphs within cells
Hi everyone, I support a user who stores narrative data within a spreadsheet. They want to know why Excel 2000 SR-1 inconsistently wraps text within a cell after the narrative exceeds 8 lines. This occurs regardless of cell height. They also want to know how to fix it so the text wraps consistently regardless of narrative length. I know the obvious answer (store the data somewhere else) - but does anyone have any more politically acceptable ideas? Thanks in advance. I don't store that much text in cells, but you may want to have them try putting some alt-enters in their text. Exce...

Pivot Query Help
I have the following query. I would like 'createnamerole' to become the columns and 'ordersourcedesc' to become the rows with the count values being the data contained between. I beleive I should use PIVOT but am unaware how. Select b.OrderSourceDesc, b.CreateNameRole, Count(b.OrderID) as Countof From (Select a.OrderID, a.CustomerNumber, a.OrderDate, a.ProductID, S.OrderSourceDesc, a.CreateNameRole From dbo.tblOrderSource S With (nolock) Inner join (SELECT O.OrderID, O.CustomerNumber, O.OrderDate, OL.ProductID, O.OrderSourceID, O.CreateNameRole FR...

Problems saving pivot table in same workbook as export query
The user exported a query into a workbook, in the same workbook she added a pivot table worksheet. She saved the workbook but only the export saved. She needs to know if the pivot table can be saved and refreshed with new data later on and saved in the same workbook from an export query. Or does it have to be in a workbook by itself and saved. Please help. ...

Filter recordset using query results
Hi all I have a form based on a query called [qry Quarterly Planning], it lists all Itineraries on the system. On this form you can filter records by specifying a Start and End Date for the [ReviewDate] and/or [Specialist]. It is a subform on a main unbound form, lets call this Subform1. Along side this I have another subform (Subform2) which displays ReviewDates that exist against an Itinerary. In other words Subform1 has a start date of an activity and if the activity lasts longer than 1 day, then the additional dates are stored in Subform2 (ItineraryDates). Currently when I...

Query parameter "refresh when value changes" doesn't refresh
In excel I set up a parameter query. In the query parameter properties I checked "Get the value from the following cell" and checked the box "Refresh Automatically when cell value changes". It's doesn't refresh automatically when I change the paramenter value and I still have to manually refresh. Any clues why the automatic refresh isn't working? ...

With a Query in Access 2007, How can I Create This Query
I need a query that will list all records in table 1 for which there are no auditor records (Table 3). Somehow, I need to use the relationship between tables 2 and 3 to find what's not in table 1. The following query gives me a list of all records that do have auditor records. I'm at a dead end on this one. Query SELECT PTOTNamesTbl.PTOTAuditingTherapist, PTOTAuditingTherapist.PTOTFirstName, PTOTAuditingTherapist.PTOTLastName, AuditDetailInitialEval.TherapistLastName, PTOTNamesTbl.PTOTFirstName, PTOTNamesTbl.PTOTLastName, AuditDetailInitialEval.Medicare, AuditDet...

Query on "Yes" Brings Back both
Hi, I have created a table where the value is Yes/No - text with default as No. I have a query in which I am trying to bring back only the "Yes", but both yes and no are being shown. Any ideas? Thanks It keeps saying "Data Type mismatch" but how can that be when I created the query from scratch? SELECT [all 05].Examiner, [all 05].[Risk Number], [all 05].Company, [all 05].Address1, [all 05].Address2, [all 05].City, [all 05].State, [all 05].[Postal Code], [all 05].Assn, [all 05].[07 Group], [all 05].[Retainer for 2007], [all 05].[Claim Salutation], [all 05].[Cla...

dropdown data for form fill-in
I've created a form and it includes selecting data from a dropdown box, and it populates my table. However, when I run a query, it won't pick up the record that has the data criteria from the "dropdown" list. Help. Just a guess, but it sounds to me like you may have one of those evil table lookups defined at the table or query level. If this is the case, the value being stored in the table may not be as it appears. Please see the 2nd Commandment of Access here: The Ten Commandments of Access http://www.mvps.org/access/tencommandments.htm If you see a combo...

Duplicates not added in a Cross Tab Query
I have a table that contains transactions amounts, transaction codes, and dates. I have created a Cross Tab query that that has the Transaction Code and the Row Heading, the Date as the Column heading, and the Transaction Amount as the value (summed). My goal is a to produce a query that shows the Sum Transaction amount by code and date. The issue I have is that when I have a duplicate transaction amount on the same day, which is very frequent, the Transaction Amount does not sum all of the duplicates. If the amounts are different, they are added. If they are the same, the query on...

Need to automate print reports-- for diff values in the query field
I want to email each of my sales reps a snapshot of a Access report that lists their sales invoices for the month (only their sales invoices) Presently I do this by running a macro that runs a query, report & emails a snapshot. I physically enter the value for a field [REP] as a variable for the query. It works fine, but we now have 20 reps so I have to run this macro and type in each of teh 20 rep codes, and I trasnpose. I need to create to automate this so that Access can change the query based on thte [REP] field in a table. I have table #1 that has one record for each rep ...

Macros and Queries
I have someone who has queries and macros on a spreadsheet and they would like to get those on someone else's computer for that person to use as well. How do you transfer those to another computer to use in another spreadsheet? Thanks in Advance, Trisha ...

removing a web query
i have managed to make 2 web queries so that when i run them they both ask for input how can i simply remove one of them thank for the help highlight it in the worksheet and do Edit=>Clear => All -- Regards, Tom Ogilvy "bd" <bryan.davey@virgin.net> wrote in message news:yDAkf.16602$7p5.2880@newsfe4-win.ntli.net... > i have managed to make 2 web queries so that when i run them they both > ask for input > how can i simply remove one of them > thank for the help ...

Microsoft query does not recognize tables in Excel spreadsheet
Using Excel 2007 12.0 SP 2 on Windows XP I have created an Excel spreadsheet, C:\Temp\PracDbase.xlsx with two tables: Table1: X Y 1 1 2 2 Table2: X Y 1 1 2 2 Both of these are on the same worksheet, sheet1. The first table occupies the range A1:B3, the second occupies the range D1:E3. Both of these ranges have been converted to tables via Home/Format As Table. If I look up names under Formulas/Name Manager, both of these table names are there and they refer to the correct range. ie, I am certain that the tables Table1 and Table2 exist in C:\Temp\PracDbase.xlsx and...

Query Setup with Two Tables
Is this possible in a query? You have Table A Table B There is a link or join between the two tables. Table A is the main table, B is a sub or secondary. When you view the records in datasheet, you see what is there. I want to see ALL THE OTHER records, the ones that do not meet that requirement, they do not have any records in table B. So they are being filtered out and I want to only see those records. Curtis Double click on the join line. Select option 2 or option 3 whichever says ALL records in Table A and matching in Table B. This should work AS LONG AS...

Query Problems in access 2007
Hi,i am a new user to access 2007.i have a select Query with partial name input.The criteria goes like this-Like * & [Enter a Part Number] & * Intially,everything was fine.But whenever i am entering data to my database.and i tried to use the query,it always prompt me twice.I tried closing access and opening it again.But it stil prompts me twice.Only when i delete and re-enter the criteria again,then it works fine again.Please help,is there something wrong with my way of writing the criteria?Thank you. Try putting double quotes around both asterisks. PC Datasheet Providing Cust...

Having trouble with web query, importing not consistent.
Hi, I have a nice book set up with web querys and it's doing a fantastic job for me, except for one little problem: The data does not always get imported to exact same place in the sheet, I'll clarify. The first column is supposed to contain a name, while the next 10 columns is supposed to contain data after running a web update. That's the ideal situation every time, and i'm updating once a day. Now the problem is that somedays the data is imported in a different way. For instance the name which is supposed to end up in the A column gets split up so it now takes up t...

return primary key for record after append query
Is there a way to find the primary key for a new record added to a table using an append query in VBA? I need to assign that to a variable so that I can use it an a second append query to a different table. For example: tblCust: CustID (pk), CustName, CustAddress tblOrder: OrderID (pk), CustID (fk), ProdID If I use append query to add CustName and CustAddress to the tblCust table, how do I get the CustID for that record to use in an append query to add records to the tblOrder table? It is possible to query the last key value like this: Function ShowIdentity() As Variant ...

Need shaded area in gantt chart to show lines around each grid squ
Hi - I have created a gantt chart using excel 2002. I have a shaded area on my chart from 9/1-9/10/07. Can I make the shaded area show lines through it? This would make it much easier to follow down the page on the date selected. I know it is easily done in excel when you fill in say 3 squares in yellow and then highlight the area and select borders. I tried doing this in my chart, but can not make it work. Any help would be appreciated. -- Karen W. ...

Use Same Variable multiple times in a query with different criteri
ok i have this variable i am trying to use twice with different criteria and it doesnt work help after i do with i want to count them November: Date_mailed (Variable Name) tblRecruitment2 (Table name) Between #11/1/2009# And #11/30/2009# (Criteria) October: Date_mailed tblRecruitment2 Between #10/1/2009# And #10/31/2009# I think you want to count? If so, set the E looking button on your tool bar, which turns on the Group ON feature then change the Group On to count. -- Milton Purdy ACCESS State of Arkansas "BZboarding101" wrote: ...

Cursor position jumping around during document composition
I recently purchased an ASUS laptop with Windows 7 pre-installed. I have been have major problems with cursor control when composing documents, even now as I type. If I don't continue typing at lightning speed, the cursor position changes in the document, sometimes wipes out text just typed or the computer jumps to another open Microsoft application. Any suggestions on the source of this problem and how to correct it? Best regards, EA ...

Assigning a banding marker in a query
I have a table that has a numeric column "sales" that ranges from 1 to 1,000,000. In a query, I am attempting to assign a banding marker to each record. I have 200 bands so an IIf statement won't work. So for instance, one of the 200 bands that is >=22 and less than 29 called "Quite Low". How can I append the "Quite Low" marker to a new column, "band", in my query for records that fit the criteria. In the end, each row in the query should have a value for a similarly defined range in the "Band" column. Thanx - John Build ...

USe a Parameter query
I have a simple query that I wish to use to search for titles of Music CD's. I wish to establish a parameter query that will allow the user to enter a word and the query looks for that word in the title. I have lried .....Like *[Enter the word]* in the query designand various combinations in between but to no avail. Can it be done Thanks George try to use under the "Title filed " Like "*" & ["Enter the Word" ] & "*" so it can filter that word out of your database. "George" wrote: > I have a simple query that I w...

Multiple domains in Query-based Distribution List
Hi Everyone I would like to find out if/how it is possible to setup a query-based DL with the following: Include: All users with email address @domain1.com **OR** all users @domain2.com I know you can add multiple domains/email addresses but what it effectivley does is look for All users with email address @domain1.com **AND** all users @domain2.com i.e The object has to satisfy both the criteria. I need a DL that picks up the users if they satisfy either/or. Your assistance is greatly appreciated Regards Here's a response I gave to a similar issue that was posted a few days ago ...