Simple Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database which I imported from Excel. It has customers, accounts and
a flag to say whether the sccount is live or dead together with other data.

My problem is I need to create a query that only shows me what customers
have only dead accounts (i.e. I do not want to see customers if they have
live and dead accounts). Is there an easy way to do this?

I don't mind if I have to extrac the live data first and then somehow
compare this agains the whole list and only show the dead ones where there is
no live.

Any help would be appreciated.

Thanks
 
One method

SELECT Customers.CustomerID
, Count(accounts) as TotalAccounts
, Abs(Sum(FlagField="Dead")) as CountDeadAccounts
FROM Customers
GROUP BY Customers.CustomerID
HAVING Count(Acccounts) = Abs(Sum(FlagField="Dead"))

Another method
-- Build a query getting all customers WITH a live account.
-- User that and the original table in a unmatched query to get all those
with only dead accounts.

Query saved as QLiveAccounts
SELECT Customers.CustomerId
FROM Customers
WHERE FlagField = "Live"

SELECT Customers.*
FROM Customers Left Join QLiveAccounts
On Customers.CustomerId = QLiveAccounts.CustomerID
WHERE QLiveAccounts.CustomerID is Null

IF your table and field names have no spaces, you can do the whole thing in
one query.
SELECT Customers.*
FROM Customers Left Join
(SELECT Customers.CustomerId
FROM Customers
WHERE FlagField = "Live") as QLiveAccounts
On Customers.CustomerId = QLiveAccounts.CustomerID
WHERE QLiveAccounts.CustomerID is Null
 
John, thanks for your speedy reply. Ia ma having trouble applying the
framework of your answer to my query.

My table is called MN&C Direct All (customers) and the fields are
Short Name = Customer
Account No = Accounts
Current Y/N (live or dead)

Is it just a question of substituting these into the query? Does it matter
that table and field names have spaces?
What did you mean by FlagField?
 
Yes, it does matter that the table and field names have spaces. Access
won't allow you to embed a subquery in query. So, you can't use the last
example. Spaces and other special characters (such as the &) mean that you
HAVE to use [] around all the names that contain these characters and it
also means that there is some functionality that is not available in Access
(such as subqueries). It makes life harder.

The original response was given with generic names, since you hadn't posted
the field names and table name or the field types. I still am not
completely sure I understand what your table and field names are and more
importatly THE TYPE of the fields. I think that what you need is.

Save the following query as q_LiveAccount
SELECT [Short Name]
FROM [MN&C Direct All]
WHERE [Current Y/N] = True

You add that using the query grid by opening a new query
--Select your table
--Select the Short Name field
--Select Current Y/N field and set the criteria to True (if this is a yes/no
field)
Save that as QLiveAccounts

Open another query
--Select your table
-- Select the saved query
-- drag from Your Table Short Name to the query short Name to set up a
relationship
-- double click on the line and select Show ALL yourtable and only those the
query
-- Add the fields from your table that you want to see to the grid
-- add short name from the query and set the criteria to Is Null

The SQL should end up looking something like the following
SELECT [Short Name]
FROM [MN&C Direct All] LEFT JOIN qLiveAccounts
ON [MN&C Direct All].[Short Name] = qLiveAccounts.[Short Name]
WHERE qLiveAccounts.[Short Name] is Null

If you want to see the SQL, select view: SQL from the menu
 
John,
thanks for your patience.
I have tried the query that you sent me and I still get matches for items
that have live (Current Y/N = "Y") and dead (current Y/N = "N"). What I need
to extract is only those short names from my second table where they are just
current Y/N = "N" from my first query.

If it is easier to do the subquery I can change the names on the data
imported to Access to not contain any spaces.

John Spencer said:
Yes, it does matter that the table and field names have spaces. Access
won't allow you to embed a subquery in query. So, you can't use the last
example. Spaces and other special characters (such as the &) mean that you
HAVE to use [] around all the names that contain these characters and it
also means that there is some functionality that is not available in Access
(such as subqueries). It makes life harder.

The original response was given with generic names, since you hadn't posted
the field names and table name or the field types. I still am not
completely sure I understand what your table and field names are and more
importatly THE TYPE of the fields. I think that what you need is.

Save the following query as q_LiveAccount
SELECT [Short Name]
FROM [MN&C Direct All]
WHERE [Current Y/N] = True

You add that using the query grid by opening a new query
--Select your table
--Select the Short Name field
--Select Current Y/N field and set the criteria to True (if this is a yes/no
field)
Save that as QLiveAccounts

Open another query
--Select your table
-- Select the saved query
-- drag from Your Table Short Name to the query short Name to set up a
relationship
-- double click on the line and select Show ALL yourtable and only those the
query
-- Add the fields from your table that you want to see to the grid
-- add short name from the query and set the criteria to Is Null

The SQL should end up looking something like the following
SELECT [Short Name]
FROM [MN&C Direct All] LEFT JOIN qLiveAccounts
ON [MN&C Direct All].[Short Name] = qLiveAccounts.[Short Name]
WHERE qLiveAccounts.[Short Name] is Null

If you want to see the SQL, select view: SQL from the menu


steve said:
John, thanks for your speedy reply. Ia ma having trouble applying the
framework of your answer to my query.

My table is called MN&C Direct All (customers) and the fields are
Short Name = Customer
Account No = Accounts
Current Y/N (live or dead)

Is it just a question of substituting these into the query? Does it matter
that table and field names have spaces?
What did you mean by FlagField?
Click to expand...
Click to expand...
 
Back
Top