Help with Access query

[robwilsonirl]

Hi
I've an access query which i am trying to write but getting very confused and am considering writing it as a VBA macro.

If anyone could help with the SQL query, i'd be very grateful.

My issue is as follows.

I have users who run processes for a given account. Each process contains a task. One particular task is called the "Sweep" task. This task can also be a process of the same name.

e.g.

Process               Task       Account
Trade Capture       Sweep       123
Extrap               Sweep       123
Sweep                Sweep       123
Sweep                Sweep        456 
Trade Capture       Sweep        789

So, you can see above that the user ran the Trade Capture process and they ran the Sweep process. So, there is unnecessary of the sweep process twice.

I want to figure out how to list all the accounts which ran a Non-sweep process and also ran a sweep process. In the above data, i would like to see all the first 3 rows. I would not want to see row 4 or 5, as there is no duplication of the sweep task.

Any help appreciated : )
Rob

 

[jdraw]

It sounds confusing, can you tell us more about your tables and relationships? Can you also provide a brief description of what your database is about in plain English?

 

[pwbrown]

I'm a bit confused, so you want to select records where the account has more than 1 process which includes a sweep process? So what is the Task column with all the sweep's?

 

[robwilsonirl]

Sorry, i'll try explain it a bit better.

There are more than just Sweep tasks. But i am only interested in extracting tasks of type Sweep. This is why i am showing the task column in my example.

There can be 1 to many tasks in a Process. The data i have is usgae data extracted from a fund accounting system. I'll rename the process names to make them more meaningful.

In the fund world, someone should open the fund. So, we would have the Open process, which has the Open task.
Later in the day, the fund is priced. So, we have the Pricing process. In this process, we might have the Sweep task and the Pricing task.

So, we want users to run the Open process and the Pricing Process.

However, there exists a Sweep process, which also runs the Sweep task.

Some users are running the Sweep process - but, the sweep task is being already being run in the Pricing process. So, they are duplicating effort.

Process           Task              Account
Open              OpenTask          123
Pricing           Sweep             123
Pricing           Pricing           123
Sweep             Sweep             123

In the above example, if you look at the task column, you can see Sweep was run twice for account 123. This is essentially what i want to catch.

I want to see duplicates where task is "sweep". But, of the duplicates, at least one must be a Sweep process AND at least one must be a NON-Sweep process. i.e. if there was just one sweep process, the sweep task would only have been run once. So, i wouldn't want to see it. if the only process was a Pricing process, then again - there would only be one sweep task run and so, no duplication. So, i wouldn't want to see this either.

In my above example, the reason i want to see it is because i have two sweep tasks - one of which is a Sweep Process and one of which is a non-Sweep process (Pricing in this example).

Has that made it any clearer ?

Thanks
Rob

 

[jdraw]

Please post a jpg of tables and relationships. You may have to zip it.

 

[pwbrown]

Ok just so this is clear to me you want to run a query which will find a user who has more than two sweep's in the task, with one sweep in the process and one other non sweep in the process?

What if there is:
sweep sweep 123
sweep sweep 123

 

[robwilsonirl]

@jdraw - this is just a single table imported from Excel. So, i don't have any relationships diagrams.
I will need to manipulate (rename, remove sensitive data etc) the table, as i wouldn't be able to post some of the data. But, i will try and get a modified table up today/tomorrow

@pbrown. You are almost correct in what i am looking for except the example you posted would not flag. This is because when there is a duplicate of the sweep task, there needs to be at least 1 non-Sweep process. In your example, both processes are Sweeps - so, i wouldn't want to see it.

You are correct saying the users are essentially doing a duplication of the sweep. but, for the purposes of what i am after, i need to find the ones with
1) at least 1 non-Sweep Process
AND
2) at least 1 Sweep process

for a given account

Thanks for your help guys
Rob

 

[PeterF]

You can create a query and reference your table twice link them and place the right filter.
Something like:

SELECT YourTableName.Account, YourTableName.Process, YourTableName.TasK, YourTableName_NonSweep.Account, YourTableName_NonSweep.Process, YourTableName_NonSweep.TasK
FROM YourTableName INNER JOIN YourTableName AS YourTableName_NonSweep ON YourTableName.Account = YourTableName_NonSweep.Account
WHERE (((YourTableName.Process)="sweep") AND ((YourTableName.TasK)="sweep") AND ((YourTableName_NonSweep.Process)<>[YourTableName].[Process]) AND ((YourTableName_NonSweep.TasK)<>[YourTableName_NonSweep].[process]));

 

[Brianwarnock]

I think that you can find the account numbers where this condition applies with a simple query that counts the sweep tasks per account and if more than 1 checkcks to see if there are less sweep processes , in which case one sweep task has a none sweep process.

I called the table tablesweep in my code

SELECT tblsweep.account
FROM tblsweep
GROUP BY tblsweep.account
HAVING (Sum(IIf([task]="sweep",1,0))>1) AND (Sum(IIf([process]="sweep",1,0))<Sum(IIf([task]="sweep",1,0)));

Brian

Edit
To get all of the details for these accounts join this query back to the table in a second query on account and run the second query

 

[robwilsonirl]

Apologies, folks. I have been sidetracked with other work and haven't had a chance to get back to this yet.
Hopefully i will in the next week or so. Just wanted to say thanks for the replies and i will definitely get to them, try them out and revert : )

 

[robwilsonirl]

I went with BrianWarnocks approach and mostly have what i need

SELECT account, [Process Name], [Date/Time]
FROM statsdata
WHERE account in (
SELECT account
FROM statsdata
GROUP BY account
HAVING (Sum(IIf( [Task]="sweep",1,0))>minTasks) AND (Sum(IIf([Process Name]="sweep",1,0))<Sum(IIf([Task]="sweep",1,0))) AND (Sum(IIf([Process Name]="sweep",1,0) >0))  )
ORDER BY account, [Date/Time];

How now can i pull out the count as well ?

e.g.

if I have

Account    Process Name    Date/Time
123         Trade Capture    25-feb-2013 1:10pm
123         Sweep             25-feb-2013 1:13pm  
123         Sweep             25-feb-2013 1:16pm

I am trying to get

Account    Process Name    Date/Time                 count 
123         Trade Capture    25-feb-2013 1:10pm      3
123         Sweep             25-feb-2013 1:13pm     3  
123         Sweep             25-feb-2013 1:16pm     3

Thanks again to everyone for their help

 

[Brianwarnock]

Well now I am a simple sort of guy and I do things the simple way, you have used "my" query as a sub query whereas I said join it back to the table to get extra data on the accounts, ie use cascading queries. If you do this you can include a Count(*) as AccountCount in the 1st query and then select this field in the next query.

Brian