Query Criteria

[GeorgeMc]

I am new to access so I hope someone can point me in the right direction.

I have a database that I need to query by account number and invoice type.

Extracting unique single records is ok but I can not work out how extract multiple records.

If the account number has several records(rows) and mixed invoice types(credit and invoice) is
is there a way to extract all records for an account number if they are just type credit.

account number type
123456 credit
123456 credit
123456 credit
234561 invoice
234561 credit
234561 invoice
345612 invoice
345612 credit
345612 credit

I would just want to extract account 123456

Any help would be appreciated

 

[Uncle Gizmo]

>>> Extracting unique single records is ok <<<<

How did you do this?

 

[GeorgeMc]

The database is an imported excel spreadsheet, all I did was determine the duplicate and unique records first by false for unique and true for duplicate.

That way any unique record will have false attached to it.

 

[Uncle Gizmo]

I'm not sure what you want?

Something like this (see attached)

sorry the attachment facility does not appear to be working.

 

[Jon K]

Try this query.

SELECT DISTINCT [account number]
FROM [TableName]
WHERE [account number] not in (Select [account number] from [TableName] where type<>"Credit");
.

 

[GeorgeMc]

Thanks Jon, that pulls the first account number that meets the criteria, what has to be changed to extract other account numbers that meet the same criteria.

 

[Jon K]

See the attached database, which contains a table with these records:-

account number type
111111 credit
222222 invoice
222222 credit
222222 credit
333333 credit
444444 credit
444444 credit

When the query is run, it returns these three account numbers:-

account number
111111
333333
444444

So the query should be able to retrieve a list of account numbers, not just the first account number.
.

 

[EMP]

...... that pulls the first account number that meets the criteria, ....

As Jon's sample database shows, the Distinct predicate returns a non-duplicate list.

Did you by any chance use the Top 1 predicate instead of Distinct? Top 1 returns the first row from the list.

^

 

[GeorgeMc]

Thanks again for your help Jon, I did say I was new to access, know I now the difference between query design view and sql view, I was trying to insert your string into the query design.
One of the things that puzzles me is why the performance for this query should be so poor, it froze searching the full database of 20,000 rows, when I reduced the search to 8500 rows it still took 12 minutes.
Is there any way to improve the search performance?

EMP the answer to your question is the the first paragraph above but thanks for showing an interest in helping.

 

[Jon K]

The slowness is caused by Distinct and the inefficient Not In.

Sometimes indexing the necessary fields and splitting a query into two and linking them with a join may help speed it up.

In table design, index the account number field and the type field. Then try these two queries.
qryOne:-
SELECT [account number]
FROM [TableName]
WHERE type<>"Credit";

qryTwo:-
SELECT DISTINCT [TableName].[account number]
FROM [TableName] LEFT JOIN qryOne ON [TableName].[account number]=qryOne.[account number]
WHERE qryOne.[account number] Is Null;

Run the second query.

Hopefully this can improve performance to a significant extent, though it will still take some time to compile a unique list with "DISTINCT".
.

 

[GeorgeMc]

Thanks Jon that worked really well, if I wanted a list of only mixed credits and invoices I suppose that If I can get a full list then take the take the credits out with WHERE Not type<>"Credit"; that would work?

Is there a full list of expressions and their use so I could work some queries out.

 

[Jon K]

To get a list of only mixed credits and invoices, you can use two Totals Queries:-

Query1:-
SELECT [account number], type
FROM [TableName]
GROUP BY [account number], type

Query2:-
SELECT [account number]
FROM Query1
GROUP BY [account number]
HAVING Count([account number])=2

Run Query2.

Without the need of a Join and Distinct, they should run very fast when the two fields are indexed.
.

 

[Jon K]

The fastness of Query2 above for getting a list of "only mixed credits and invoices" has led me to rethink the issue and arrive at using it to retrieve lists of "credits only" and "invoices only".

qryCredits only:-
SELECT DISTINCT [TableName].[account number]
FROM [TableName] LEFT JOIN Query2 ON [TableName].[account number]=Query2.[account number]
WHERE [TableName].type="credit" And Query2.[account number] Is Null;

qryInvoices only:-
SELECT DISTINCT [Tablename].[account number]
FROM TableName LEFT JOIN Query2 ON [Tablename].[account number]=Query2.[account number]
WHERE [TableName].type="invoice" AND Query2.[account number] Is Null


As they are based on the fast Query2, they should therefore run much faster than the original method employed in retrieving "credits only" in qryOne and qryTwo in post #10.
.

 

[GeorgeMc]

I thought I was just getting the hang of the structure of the query criteria but trying to add further columns to the database wont work with the group by for me, is there away of adding to the output.

I am also not sure it is working correctly as the output does not give any duplicate account numbers, for mixed there should be many duplicate account numbers.

 

[EMP]

I have been watching this thread. From the start, it was about retrieving unique lists of account numbers, so it was a little obscured that what you wanted was all the account numbers including duplicates for mixed.

Anyhow, now that you have a list of unique account numbers for mixed in Query2, you can easily link it back to the original table in a new query to get all the mixed records from the table.

Similarly for all the records of credits only and invoices only.

^

 

[GeorgeMc]

You are right EMP I have probably confused the issue by my use of unique, in my mind I have three types of account that I believe are unique in the database
Invoices Only
Credits Only
Mixed invoices and Credits

Extracting invoice only with multiple and single records per account
Extracting credits only with multiple and single records per account
Extracting mixed only

I can with the sql provided by Jon achieve the first two but even with that I am not able to work put how to extract the mixed accounts.

The group by solution is only giving me one line per account where there must be at least two also I cannot add further columns.

 

[EMP]

You can link Jon's Totals Queries in new queries to extract records from the table.

See my three new queries in the attached database.

^

 

[GeorgeMc]

Absolutely amazing, thanks very much EMP and Jon.

I will spend some time working out how you have structured the queries to get the end result.

 

[GeorgeMc]

I have been trying to develop the database and have come across a problem. I tried to introduce another type (debit memo) that would be treated like an invoice and thought that by adding this to the invoice query it would be counted in the extraction. It was but it threw up an anomaly in that the account that had an invoice, a credit and a debit memo appeared in the invoice only list and the credits only list but not the mixed list.

I am now confused again and I don’t understand the logic of the mixed type query, would you please explain.

 

[EMP]

I tried to introduce another type (debit memo) that would be treated like an invoice

See the database, which treats 'debit memo' as if it is 'invoice'.

To retain Jon's queries as much as possible, I just renamed Query1 to Query0 and added a new Query1 based on Query0. And I added 'debit memo' in the query 'qryInvoices only'.


The logic:
Jon used a Totals Query (Query1) to group the types of each account number, then he extracted (in Query2) from Query1 a list of account numbers that have more than one type for the mixed.

He then used two unmatched queries (qryDebits only, qrrInvoices only) on the table and Query2 to get the account numbers of debits only and of invoices only.

Then, as you have seen, I linked the table to his qryDebits only, qryInvoices only and Query2 in three new queries to get the records from the table.

^