Hello all,
I have the following data (Note: dates in dd-mm-yyyy format)
ID DEPT TARGET70 TARGET90 TARGET100
-- ---- -------- -------- ---------
12 ACCT 12/06/2005 31/07/2005 21/12/2005
14 MKT 30/08/2005 02/09/2005 11/04/2006
19 ACCT 13/04/2004 13/05/2005 22/08/2005
20 SAL 12/12/2005 20/12/2005 12/02/2006
21 SAL 21/03/2004 21/03/2005 21/09/2005
etc. etc.
It basically shows what the target dates (TARGETxx) are for certain
stages of a deparment's (DEPT) different projects (ID).
What I have to do next, is proving to be quite trivial to me. Can
someone please help?
I must compare the target dates to a certain date (which is stored in a
global variable, say gdatTheDate), and then return all the projects
(ID) for which a target date is later than gdatTheDate. But it (the
amount of later dates) has to be counted for each dept and presented as
follows:
Say gdatTheDate = 05/08/2005
Then the following is returned
DEPT NUM70 NUM90 NUM100
---- ----- ----- ------
ACCT 0 0 2
MKT 1 1 1
SAL 1 1 2
I know how to use the global variable in a query, but how do I
implement the query to do the counting and grouping etc.?
Any help would be appreciated. Thanks,
J 4 1668
Jean, what will you do when a project acquires another target? Add a
column, I suppose. But then this column can accept values for *all*
projects, whether they be running or not.
The following table design is more restrictive, in that it does not
allow spurious values:
(PROJECT, DEPT, TARGET, TARGETDATE)
It is fairly easy to do a crosstab query on this table to get the
result you want.
If you cannot change the table's design, you may resort to summing, in
stead of counting,
like this
SELECT dept, sum (IIF (target70>TheDate, 1, 0)) AS num70, <repeat for
other targets>
FROM targets
Jean, what will you do when a project acquires another target? Add a
column, I suppose. But then this column can accept values for *all*
projects, whether they be running or not.
The following table design is more restrictive, in that it does not
allow spurious values:
(PROJECT, DEPT, TARGET, TARGETDATE)
It is fairly easy to do a crosstab query on this table to get the
result you want.
If you cannot change the table's design, you may resort to summing, in
stead of counting,
like this
SELECT dept, sum (IIF (target70>TheDate, 1, 0)) AS num70, <repeat for
other targets>
FROM targets
Hi,
I have actually already had a suggestion to change the table design;
unfortunately I am accessing this data from an external ODBC source and
this is therefore not under my control. The data as I gave it in my
first post was a query that I created. Is it then possible to set up
another query that will have your suggested column headings? I.e.
(PROJECT, DEPT, TARGET, TARGETDATE)
I think I will follow your summing suggestion in the meantime. I
imagine it to be rather complex to set up a proper query for those
column headings, maybe I am wrong. But thanks for your help, it
certainly makes me one step further in finishing this job and
undertanding the problem.
Kind Regards
J
Well, if you use the Wizard for Crosstab Queries
(Queries | New | Wizard Crosstab Queries)
Access does something like the following:
TRANSFORM Max(targets.targetdate) AS MaxDate
SELECT targets.project, targets.dept, Max(targets.targetdate) AS Total
FROM targets
GROUP BY targets.project, targets.dept
PIVOT targets.target;
The result of this query is a table with
- Row headings: Project, Dept
- Column headings: Target
- Under each target heading: max(targetdate) for each combination of
Project, Dept, Target (blank if this combination is not present)
- A column called Total: max(targetdate) for every row
You may eliminate the Total column by editing the query This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Matthias Haffke |
last post by:
Ok, this is a tricky question for the pro's:
My access sheet:
line, id a, id b, val%
----------------
1, a, ac, 0.04
2, a, ac, 0.28
3, a, ac, 0.015
4, a, ac, 0.205
|
by: pierrelap |
last post by:
Hello, I need to code a query that:
1-counts the number of time two companies have been in a deal together
2-in the five years that preceded the deal
Lead Participant DealDate
AAA BBB ...
|
by: Jean |
last post by:
Hello all,
I have the following data (Note: dates in dd-mm-yyyy format)
ID DEPT TARGET70 TARGET90 TARGET100
-- ---- -------- -------- ---------
12 ACCT ...
|
by: Hyphessobricon |
last post by:
Hallo,
Indeed, a count of a query with a group by function gives more records than
there are and so for-next structures don't function.
How is this to be mended.
Anyone? Everyone in fact....
|
by: Starke |
last post by:
I have a table where Im counting records that have a two certain
criteria. My Question is, If one criteria is met, but the other isnt
instead of not retrieving the record can the count value...
|
by: alritedonthaveacow |
last post by:
hi
i have an access query that draws data from a products table and a stock table. i have a count on productid field from the prodcucts table.
the problem is that if there is no records in the...
|
by: DeanL |
last post by:
Hi everyone,
I have a subform in datasheet view that is used to display the
contents of a single table. I have another subform with a query
feeding it to give a count of how many particular...
|
by: panos100m |
last post by:
<root>
<row>
<col name="Surname">McEnroe</col>
<col name="Postcode">44312</col>
<col name="PolicyID">X1234565V</col>
</row>
<root>
<row>
<col...
|
by: CharlieUK |
last post by:
I have a button on a Client form that opens a form with a query as filter. If the query filters no records then the form comes up blank and doesnt even show the close button. Im trying to use code to...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |