Help Required on Query Building in VBA (1 Viewer)

[yaaara]

Hello,

I hope someone can help me in resolving the following in access 2003:

I have a table with the following fields:

emp_id,emp_name,repdate,actions,duration,lob,categ ory,ltstatus

emp_id is not unique and there appears to be no primary key for this table.

the actions field stores the count of all actions recorded under ltstatus and their duration is logged under duration field.

there can be multiple actions for each lob & category.

What i need is the following:

All unique actions need to be sidelined from the table and then for each unique date (repdate), emp_id, lob & category combination, i need the sum of duration and sum of action (the number of actions is currently 8 and hence it makes 8*2=16 fields to record the sum of duration and sum of action for each action)

I am currently able to accomplish the task by looping through the records but it is taking a very very very long time (due to the loops of course)..

What I am looking for is a solution (a query perhaps?) which can do it faster...

Pls help.

Thanks.

 

[yaaara]

PS: Please if someone can resolve it today, I'd be highly grateful...

 

[chergh]

You could post the code you currently have so we can see exacty what you need to do.

 

[georgedwilkinson]

I can only guess you haven't used the query builder. All of this stuff is kind of built in.

Go into "create query in design view", double click on your tables, make sure the relationships are right (if any), double click on all the fields you want to group by, double click on the field you want to aggregate (sum, average, count, whatever), click on group by, on the field you want to aggregate, select the correct aggregation type. When you've got it right, run it. If it's wrong, post the sql it generates here along with a description of the problem and somebody'll take a look at it.

 

[dkinley]

These links provide great tutorials for MS Access ...

http://www.techonthenet.com/access/index.php

http://www.sienaheights.edu/personal/csstrain/Access2007.htm

http://www.functionx.com/vbaccess/

-dK

 

[yaaara]

Hi and thanks for the replies,

I can't use the query builder as the columns I need to create originate from unique records within a table field (the ltstatus field) and so I won't be able to accomplish what i want..

I'm not sure if I'm able to explain it properly but I've tried in the first instance.. The key here is to extract the unique values from within the ltstatus field and then getting the sum of each instance together as a single record.

example:

if there are 2 actions defined under ltstatus field viz. Pending & Closed then the output would be something like this:

emp_id,emp_name,repdate,lob,category,sum of pending duration,sum of pending actions,sum of closed duration,sum of closed actions...

I hope this explains something better?

 

[georgedwilkinson]

I think I get it. You have data in a concatenated field that you want to aggregate. Is that correct?

Man, that's gonna be tough. Post some of the sample raw data with a little more info and let's see what we can do.

 

[dkinley]

Hmmm ... a pointer on user's requirements only in 3 clicks of the references posted ...

http://www.techonthenet.com/access/queries/unique_values.php


Apology for the sarcasm, but it helps if you have worked on the problem somewhat so that you can better explain your problem.

-dK

 

[yaaara]

Sorry Dk, but this doesn't help..

Attached is a sample data which I need to be worked on...

As can be seen, there are multiple lob's, categories and ltstatus'

All the unique ltstatus' need to be converted in different columns as the sum of actions and sum of duration for each unique date, lob, category combination... This needs to be output in an Excel file..

I hope this explains better.

Let me know if something more is required..

 

[dkinley]

It explains somewhat better ... I apologize, but I am still not quite getting it.

Could you take the sample data in the post and put it into an Excel spreadsheet manually on how you want it presented and then post that result?

-dK

 

[yaaara]

Hello,

I have created a sample output for you to take a look..

Hope this would give you a better idea..

 

[ajetrumpet]

Yaara,

I just read through this post, and I don't think the data that you are presenting is quite clear enough. First, the Access file that you posted already has manipulated data in the one table that you have (e.g. - the "actions" field has already been "summed" it looks like, from all the unique record combinations (mentioned in the first post) in the original table)). Second, The excel file I don't think it showing congruent data. It is very hard to follow. Example - "actions" column from the top portion corresponds to one column on the bottom portion for one record, then another column for the next record...and so on.

It may not make sense to me because I am joining this discussion late, but I am just pointing it out to you...

 

[yaaara]

Hi and thanks for looking at this thread...

Unfortunately, the data in the Excel File is "As-is" and has not been manipulated and is the final output of my program (The one which I am actually using.. Only the data in blank fields have been removed...)

I understand that it may be difficult to understand in one go, but that's what my problem is as well... need a simplified solution :-(

 

[dkinley]

I took a look at the output ... I agree with Ajetrumpet .. still hard to follow because I couldn't figure out the pattern of how you obtained your numbers in the output based on the data from the posted db.

Could you elaborate further and instruct on the formulas used to do the calculation? It may be that there might not be a simple solution because of the derivation of the output and might take some time to figure out.

-dK