Need help in simple query....!

[jayanth1808]

Hi all,

Actually i am preparing a database. In that i need to develop a query in such a way i have explained it in attached picture.. i have created one query called query1 but that is giving wrong results... please check that and correct that...

Attaching database and problem picture...

Thanks

 

[khawar]

Check the query does it works now

 

[jayanth1808]

Hey... Thanks for ur reply.. but i think.. its still not giving me d results as i wanted... just iam attaching a image that shows exactly wht r d results i am expecting from that query from those tables in the attached database....

 

[wiklendt]

why does it work for you in your second screenshot and not you first? what is the difference between the two? is it just switching between Ac2007 and Ac97? or similar? maybe there's an incompatible syntax issue?

 

[wiklendt]

i got you a little bit closer. but the counts themselves are off. it could be a field type issue or something similar... i've never used sum queries before, so this is as far as i can personally take you.

what i did was change the referenced table of the "date" and "team" fields to be from the FTE_count table instead of the Volumes table.

before you go any further, i suggest you give your assets intuitive names, like tblVolumes, tblFTEcount, tblActivity.

Introduce primary keys, relationships with integrity and normalise your data (e.g., you'll at least need a tblTeam table, and possibly more). i STRONGLY urge you to AT LEAST look into data normalisation (or normalization, as the yanks call it), because this will be a MAAAAAJOR issue for you if you progress much further with your database.

 

[wiklendt]

ah! even closer! i believe this is probably what you want. has your data changed or been added to (in the database) as oppsed to the second image you posted? because some numbers now at least match, which others are a little different. but if you look at the 'Srikanth' team, it has a sum of 8, which is what your second image showed... so we must at least be getting close.

what i did this time was to change the join made by khawar... from the "Date-Date" join to a "Team-Team" join.

( i also changed you database setting to "compact on close" - keeps things nice and neat )

 

[wiklendt]

ok, actually, i think the problem may be your design - you are trying to count up two different things form two different tables into the one query - i think this is why the counts are messed up. srikanth is fine i think because it only has entries in the one table (FTE) and not in the VOlumes table.

you may need to re-think how to do these counts. i am not the person to help here, but i've at least got you this far.

btw, where did you get the other counts picture from?

 

[wiklendt]

by the way - normalisation MIGHT fix your issue (you'd have to try it to check).

 

[jayanth1808]

Thanks so much for reply.. actually i am new to database... so i dnt knw much abt normalisation n all....

The second picture... i made it manually in excel to give more clear picture abt the requirement.

but wht u said in the last post is right.... I want the data from two tables.. Is it possible?

 

[marianne]

will this solve your problem? tell us if that solves your problem.

 

[jayanth1808]

Hi marianne, Thanks so much.. i was searching for this only.. thanks alot....

 

[marianne]

does it solve your problem?

 

[jayanth1808]

Yes, it has got solved. Thanks for that.. and one more thing i wanted to in this database is...

I have the cycle times for each activity in activity table, I just wanted to have a query in such a way... It will give me the time spent on all those activities per each day group by date and team.

The below is the required format.....

Thanks in advance.

 

[marianne]

try this: is this result you need?

 

[jayanth1808]

Hi, Really Im so thankful for ur support in this...


Finally, I have come to an end with this queries.... But need ur assistance for the last time... Actually I prepared the final query called copy of query1 ( see in the screenshot) . In that I am Ok with calculated FTE field.

The problem is when i tried to pull the FTE_Count from FTE count table. even though there no records for date 04/15/09, its giving me the count. I want it to be there only respective records for that date.

Even I have tried with module code "GetFTE" also but im unable to call GetFTE function from Queries.. Plz tell me how can we call module function from queries?

Find the database in the attachment.

 

[marianne]

please attach a ms access 2000 version of your sample. I cannot open it on access 2007 version. anyway, I am available only minus 7 hours in your time there.

 

[jayanth1808]

Hey.... here is the database in 2000 format.... Please help me with those...

 

[jayanth1808]

Hi marianne, Did u have a look at it... Please help me!