Help with criteria

P

Paul B

I need a criteria or formula in a query to look at two fields, STATUS_CODE
and STATUS_DATE, what I need is to look at status code for 25-assgn and
70-wrkcomp, if the assgn date is greater than wrkcomp date, in the status
date field, I need to show that data.



So in the example below the proposal 0730874 will show up when I run the
query, how can this be done?



The SQL code is also below if that helps



Using Access 2003



Thanks





qselWorkOrdersOpenedBackUp

PROPOSAL
SHOP
STATUS_CODE
STATUS_DATE
LOGIN
SHOP_PERSON

0730874
56
10-OPEN
11/29/06 3:02:06 PM
DWISE
KLW056363

0730874
56
70-WRKCOMP
1/5/07 3:44:04 PM
DAGNEW
KLW056363

0730874
56
25-ASSGN
11/29/06 3:26:32 PM
PAULB
KLW056363

0730874
56
25-ASSGN
1/8/07 8:09:32 AM
PAULB
KLW056363

0730700
56
10-OPEN
11/28/06 2:33:11 PM
DAGNEW
DLH060473

0730700
56
70-WRKCOMP
12/12/06 4:00:20 PM
DAGNEW
DLH060473

0730700
56
25-ASSGN
11/28/06 2:33:30 PM
DAGNEW
DLH060473








SELECT ROOT_AE_P_PHS_E.PROPOSAL, ROOT_AE_P_PHS_E.SHOP,
ROOT_AE_P_PST_E.STATUS_CODE, ROOT_AE_P_PST_E.STATUS_DATE,
ROOT_AE_P_PST_E.LOGIN, ROOT_AE_P_PRO_S.SHOP_PERSON

FROM (ROOT_AE_P_PHS_E INNER JOIN ROOT_AE_P_PST_E ON ROOT_AE_P_PHS_E.PROPOSAL
= ROOT_AE_P_PST_E.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S ON
(ROOT_AE_P_PHS_E.SORT_CODE = ROOT_AE_P_PRO_S.SORT_CODE) AND
(ROOT_AE_P_PHS_E.PROPOSAL = ROOT_AE_P_PRO_S.PROPOSAL)

WHERE (((ROOT_AE_P_PHS_E.SHOP)="56"));
 
G

Gary Walter

Paul B said:
I need a criteria or formula in a query to look at two fields, STATUS_CODE
and STATUS_DATE, what I need is to look at status code for 25-assgn and
70-wrkcomp, if the assgn date is greater than wrkcomp date, in the status
date field, I need to show that data.



So in the example below the proposal 0730874 will show up when I run the
query, how can this be done?



The SQL code is also below if that helps



Using Access 2003



Thanks





qselWorkOrdersOpenedBackUp

PROPOSAL
SHOP
STATUS_CODE
STATUS_DATE
LOGIN
SHOP_PERSON

0730874
56
10-OPEN
11/29/06 3:02:06 PM
DWISE
KLW056363

0730874
56
70-WRKCOMP
1/5/07 3:44:04 PM
DAGNEW
KLW056363

0730874
56
25-ASSGN
11/29/06 3:26:32 PM
PAULB
KLW056363

0730874
56
25-ASSGN
1/8/07 8:09:32 AM
PAULB
KLW056363

0730700
56
10-OPEN
11/28/06 2:33:11 PM
DAGNEW
DLH060473

0730700
56
70-WRKCOMP
12/12/06 4:00:20 PM
DAGNEW
DLH060473

0730700
56
25-ASSGN
11/28/06 2:33:30 PM
DAGNEW
DLH060473








SELECT ROOT_AE_P_PHS_E.PROPOSAL, ROOT_AE_P_PHS_E.SHOP,
ROOT_AE_P_PST_E.STATUS_CODE, ROOT_AE_P_PST_E.STATUS_DATE,
ROOT_AE_P_PST_E.LOGIN, ROOT_AE_P_PRO_S.SHOP_PERSON

FROM (ROOT_AE_P_PHS_E INNER JOIN ROOT_AE_P_PST_E ON
ROOT_AE_P_PHS_E.PROPOSAL = ROOT_AE_P_PST_E.PROPOSAL) INNER JOIN
ROOT_AE_P_PRO_S ON (ROOT_AE_P_PHS_E.SORT_CODE = ROOT_AE_P_PRO_S.SORT_CODE)
AND (ROOT_AE_P_PHS_E.PROPOSAL = ROOT_AE_P_PRO_S.PROPOSAL)

WHERE (((ROOT_AE_P_PHS_E.SHOP)="56"));
Click to expand...
One way might be to save the following query,
then filter it for SHOP="56" and 25Date > 70Date...

SELECT
PHS.PROPOSAL,
PHS.SHOP,
PST.STATUS_CODE,
PST.STATUS_DATE,
PST.LOGIN,
PRO.SHOP_PERSON
(SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSOL
AND
ST.STATUS_CODE = "25-ASSGN") As 25Date,
(SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSOL
AND
ST.STATUS_CODE = "70-WRKCOMP") As 70Date

FROM
(ROOT_AE_P_PHS_E As PHS
INNER JOIN
ROOT_AE_P_PST_E As PST
ON
PHS.PROPOSAL = PST.PROPOSAL)
INNER JOIN
ROOT_AE_P_PRO_S As PRO
ON
(PHS.SORT_CODE = PRO.SORT_CODE)
AND
(PHS.PROPOSAL = PRO.PROPOSAL);
 
P

Paul B

Gary, I don't understand the 25Date > 70Date... part, in design view what
would it look like and field would I put it?
 
G

Gary Walter

Paul B said:
Gary, I don't understand the 25Date > 70Date... part, in design view what
would it look like and field would I put it?
Click to expand...

I was hoping you would start a new query
and in SQL View, replace anything there
with the following:

SELECT
PHS.PROPOSAL,
PHS.SHOP,
PST.STATUS_CODE,
PST.STATUS_DATE,
PST.LOGIN,
PRO.SHOP_PERSON,
(SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "25-ASSGN") As 25Date,
(SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "70-WRKCOMP") As 70Date
FROM
(ROOT_AE_P_PHS_E As PHS
INNER JOIN
ROOT_AE_P_PST_E As PST
ON
PHS.PROPOSAL = PST.PROPOSAL)
INNER JOIN
ROOT_AE_P_PRO_S As PRO
ON
(PHS.SORT_CODE = PRO.SORT_CODE)
AND
(PHS.PROPOSAL = PRO.PROPOSAL);

Then save the query giving it some name.

If it works correctly, you should get a
[25Date] and [70Date] field in the results for
each PROPOSAL (especially since I
spelled it correctly this time).

Then start a new query using this query
as a starting point where you apply criteria
for [SHOP] and [25Date] > [70Date]
 
P

Paul B

Gary, will give it a try at work tomorrow



Gary Walter said:
Paul B said:
Gary, I don't understand the 25Date > 70Date... part, in design view what
would it look like and field would I put it?
Click to expand...

I was hoping you would start a new query
and in SQL View, replace anything there
with the following:

SELECT
PHS.PROPOSAL,
PHS.SHOP,
PST.STATUS_CODE,
PST.STATUS_DATE,
PST.LOGIN,
PRO.SHOP_PERSON,
(SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "25-ASSGN") As 25Date,
(SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "70-WRKCOMP") As 70Date
FROM
(ROOT_AE_P_PHS_E As PHS
INNER JOIN
ROOT_AE_P_PST_E As PST
ON
PHS.PROPOSAL = PST.PROPOSAL)
INNER JOIN
ROOT_AE_P_PRO_S As PRO
ON
(PHS.SORT_CODE = PRO.SORT_CODE)
AND
(PHS.PROPOSAL = PRO.PROPOSAL);

Then save the query giving it some name.

If it works correctly, you should get a
[25Date] and [70Date] field in the results for
each PROPOSAL (especially since I
spelled it correctly this time).

Then start a new query using this query
as a starting point where you apply criteria
for [SHOP] and [25Date] > [70Date]
Click to expand...
 
P

Paul B

Gary, this is what I ended up with, it shows a 0 or -1 based on the dates,
but even when I filter it down to the last 30 days and the STATUS_CODE it
takes about 5 min. to run and that long any time a change is made, do you
see anything that could speed it up?

Thanks

SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN,
PRO.SHOP_PERSON, (SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "25-ASSGN") AS 25Date, (SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "70-WRKCOMP") AS 70Date, [25Date]>[70Date] AS Expr1
FROM (ROOT_AE_P_PHS_E AS PHS INNER JOIN ROOT_AE_P_PST_E AS PST ON
PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S AS PRO ON
(PHS.PROPOSAL = PRO.PROPOSAL) AND (PHS.SORT_CODE = PRO.SORT_CODE)
WHERE (((PHS.SHOP)="56") AND ((PST.STATUS_CODE)="70-WRKCOMP" Or
(PST.STATUS_CODE)="25-ASSGN") AND ((PST.STATUS_DATE) Between Date()-30 And
Date()+1));

Gary Walter said:
Paul B said:
Gary, I don't understand the 25Date > 70Date... part, in design view what
would it look like and field would I put it?
Click to expand...

I was hoping you would start a new query
and in SQL View, replace anything there
with the following:

SELECT
PHS.PROPOSAL,
PHS.SHOP,
PST.STATUS_CODE,
PST.STATUS_DATE,
PST.LOGIN,
PRO.SHOP_PERSON,
(SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "25-ASSGN") As 25Date,
(SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "70-WRKCOMP") As 70Date
FROM
(ROOT_AE_P_PHS_E As PHS
INNER JOIN
ROOT_AE_P_PST_E As PST
ON
PHS.PROPOSAL = PST.PROPOSAL)
INNER JOIN
ROOT_AE_P_PRO_S As PRO
ON
(PHS.SORT_CODE = PRO.SORT_CODE)
AND
(PHS.PROPOSAL = PRO.PROPOSAL);

Then save the query giving it some name.

If it works correctly, you should get a
[25Date] and [70Date] field in the results for
each PROPOSAL (especially since I
spelled it correctly this time).

Then start a new query using this query
as a starting point where you apply criteria
for [SHOP] and [25Date] > [70Date]
Click to expand...
 
G

Gary Walter

Paul B said:
Gary, this is what I ended up with, it shows a 0 or -1 based on the dates,
but even when I filter it down to the last 30 days and the STATUS_CODE it
takes about 5 min. to run and that long any time a change is made, do you
see anything that could speed it up?

Thanks

SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE,
PST.LOGIN, PRO.SHOP_PERSON, (SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "25-ASSGN") AS 25Date, (SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "70-WRKCOMP") AS 70Date, [25Date]>[70Date] AS Expr1
FROM (ROOT_AE_P_PHS_E AS PHS INNER JOIN ROOT_AE_P_PST_E AS PST ON
PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S AS PRO ON
(PHS.PROPOSAL = PRO.PROPOSAL) AND (PHS.SORT_CODE = PRO.SORT_CODE)
WHERE (((PHS.SHOP)="56") AND ((PST.STATUS_CODE)="70-WRKCOMP" Or
(PST.STATUS_CODE)="25-ASSGN") AND ((PST.STATUS_DATE) Between Date()-30 And
Date()+1));
Click to expand...

First...are these simply 3 tables in an one mdb backend?
-- not tables or views in SQL Server, Oracle, MySQL, IBM DB
-- not queries

If so, what fields are indexed?
PHS
-- PROPOSAL
-- SORT_CODE
-- SHOP
PST
-- PROPOSAL
-- STATUS_DATE
-- STATUS_CODE
PRO
-- PROPOSAL
-- SORT_CODE

At the least, PROPOSAL should be indexed in all 3 tables.
Indexes can be "expensive," but optimally all fields above
should be indexed depending upon your data.

If these are mdb tables and the fields are optimally indexed,
and performance is still slow,then the next strategy would
be to rethink the subqueries.

Correlated subqueries (where we found [25Date] and [70Date])
can affect performance. Often it helps to "divide-and-conquer."

How do these 2 queries perform?

qry25Date

SELECT
ST.PROPOSAL,
Max(ST.STATUS_DATE) As Max25Date
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.STATUS_CODE = "25-ASSGN"
GROUP BY
ST.PROPOSAL;

qry70Date

SELECT
ST.PROPOSAL,
Max(ST.STATUS_DATE) As Max70Date
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.STATUS_CODE = "70-WRKCOMP"
GROUP BY
ST.PROPOSAL;

If acceptable, then get rid of correlated queries,
and you could try just adding these 2 queries to
your query design...or, you could "divide" even
further...

qryProposalsOpenedBackOpen

SELECT
q1.PROPOSAL,
q1.Max25Date,
q2.Max70Date,
FROM
qry25Date As q1
INNER JOIN
qry70Date As q2
ON
q1.PROPOSAL = q2.PROPOSAL
WHERE
q1.[Max25Date] > q2.[Max70Date];

That ought to slice-and-dice down to
some good performance I would think.

How does it perform? Do you think you
get the correct records? I really just guessed
that you will only be concerned with the
max dates for each PROPOSAL.

Further "divide" might involve bringing only
specific SHOP to the final query...

qrySHOP

SELECT
DISTINCT
PHS.PROPOSAL,
PHS.SHOP,
PRO.SHOP_PERSON
FROM
ROOT_AE_P_PHS_E AS PHS
INNER JOIN
ROOT_AE_P_PRO_S AS PRO
ON
(PHS.PROPOSAL = PRO.PROPOSAL)
AND
(PHS.SORT_CODE = PRO.SORT_CODE)
WHERE
PHS.SHOP ="56";

How does this perform? I am not sure about
your data so maybe the "DISTINCT" is not
necessary? For the final query, it makes sense
to me that this query bring a single record for
each distinct PROPOSAL/SHOP/SHOP_PERSON.
I could be wrong...

So...your final query...

It appears you want to return

PHS.PROPOSAL, <--- qryProposalsOpenedBackOpen As qP
PHS.SHOP, <--- qrySHOP As qS
PST.STATUS_CODE, <--- ROOT_AE_P_PST_E AS PST
PST.STATUS_DATE, <--- ROOT_AE_P_PST_E AS PST
PST.LOGIN, <--- ROOT_AE_P_PST_E AS PST
PRO.SHOP_PERSON <--- qrySHOP As qS

Start a new query and add
qryProposalsOpenedBackOpen
qryShop
ROOT_AE_P_PST_E

Right-mouse click on the table qryProposalsOpenedBackOpen,
choose "Properties,"
and set "Alias" to "qP"

Right-mouse click on the table qrySHOP,
choose "Properties,"
and set "Alias" to "qS"

Right-mouse click on the table ROOT_AE_P_PST_E,
choose "Properties,"
and set "Alias" to "PST"

Join qP.PROPOSAL to qS.PROPOSAL

Join qP.PROPOSAL to PST.PROPOSAL

Double-click on the fields in the tables that you want
(as referenced above) to send them down to the grid.

Save the query.

We've indexed, divided-and-conquered, and sliced-and-diced....

If that does not help (and if I did not misunderstand),
I am out of ideas for the moment...
 
P

Paul B

Gary, first off thanks for the help, I think I am getting in deep here, not
to good with Access, the data is coming from a linked table from an ODBC
data base that I have no control over, just trying to get some data out of
it. I have a lot more experience with excel and have managed to import the
data into Excel and get the information I need with a few helper columns and
some formulas, but I would like to keep it all in Access, if I can, so I
will try and follow the instructions you gave and see it I can get it to
work that way. It will be Monday before I can try it out at work.

Thanks again for all your help

Paul

--


Gary Walter said:
Paul B said:
Gary, this is what I ended up with, it shows a 0 or -1 based on the dates,
but even when I filter it down to the last 30 days and the STATUS_CODE it
takes about 5 min. to run and that long any time a change is made, do you
see anything that could speed it up?

Thanks

SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE,
PST.LOGIN, PRO.SHOP_PERSON, (SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "25-ASSGN") AS 25Date, (SELECT
Max(ST.STATUS_DATE)
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.PROPOSAL = PHS.PROPOSAL
AND
ST.STATUS_CODE = "70-WRKCOMP") AS 70Date, [25Date]>[70Date] AS Expr1
FROM (ROOT_AE_P_PHS_E AS PHS INNER JOIN ROOT_AE_P_PST_E AS PST ON
PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S AS PRO ON
(PHS.PROPOSAL = PRO.PROPOSAL) AND (PHS.SORT_CODE = PRO.SORT_CODE)
WHERE (((PHS.SHOP)="56") AND ((PST.STATUS_CODE)="70-WRKCOMP" Or
(PST.STATUS_CODE)="25-ASSGN") AND ((PST.STATUS_DATE) Between Date()-30 And
Date()+1));
Click to expand...

First...are these simply 3 tables in an one mdb backend?
-- not tables or views in SQL Server, Oracle, MySQL, IBM DB
-- not queries

If so, what fields are indexed?
PHS
-- PROPOSAL
-- SORT_CODE
-- SHOP
PST
-- PROPOSAL
-- STATUS_DATE
-- STATUS_CODE
PRO
-- PROPOSAL
-- SORT_CODE

At the least, PROPOSAL should be indexed in all 3 tables.
Indexes can be "expensive," but optimally all fields above
should be indexed depending upon your data.

If these are mdb tables and the fields are optimally indexed,
and performance is still slow,then the next strategy would
be to rethink the subqueries.

Correlated subqueries (where we found [25Date] and [70Date])
can affect performance. Often it helps to "divide-and-conquer."

How do these 2 queries perform?

qry25Date

SELECT
ST.PROPOSAL,
Max(ST.STATUS_DATE) As Max25Date
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.STATUS_CODE = "25-ASSGN"
GROUP BY
ST.PROPOSAL;

qry70Date

SELECT
ST.PROPOSAL,
Max(ST.STATUS_DATE) As Max70Date
FROM
ROOT_AE_P_PST_E As ST
WHERE
ST.STATUS_CODE = "70-WRKCOMP"
GROUP BY
ST.PROPOSAL;

If acceptable, then get rid of correlated queries,
and you could try just adding these 2 queries to
your query design...or, you could "divide" even
further...

qryProposalsOpenedBackOpen

SELECT
q1.PROPOSAL,
q1.Max25Date,
q2.Max70Date,
FROM
qry25Date As q1
INNER JOIN
qry70Date As q2
ON
q1.PROPOSAL = q2.PROPOSAL
WHERE
q1.[Max25Date] > q2.[Max70Date];

That ought to slice-and-dice down to
some good performance I would think.

How does it perform? Do you think you
get the correct records? I really just guessed
that you will only be concerned with the
max dates for each PROPOSAL.

Further "divide" might involve bringing only
specific SHOP to the final query...

qrySHOP

SELECT
DISTINCT
PHS.PROPOSAL,
PHS.SHOP,
PRO.SHOP_PERSON
FROM
ROOT_AE_P_PHS_E AS PHS
INNER JOIN
ROOT_AE_P_PRO_S AS PRO
ON
(PHS.PROPOSAL = PRO.PROPOSAL)
AND
(PHS.SORT_CODE = PRO.SORT_CODE)
WHERE
PHS.SHOP ="56";

How does this perform? I am not sure about
your data so maybe the "DISTINCT" is not
necessary? For the final query, it makes sense
to me that this query bring a single record for
each distinct PROPOSAL/SHOP/SHOP_PERSON.
I could be wrong...

So...your final query...

It appears you want to return

PHS.PROPOSAL, <--- qryProposalsOpenedBackOpen As qP
PHS.SHOP, <--- qrySHOP As qS
PST.STATUS_CODE, <--- ROOT_AE_P_PST_E AS PST
PST.STATUS_DATE, <--- ROOT_AE_P_PST_E AS PST
PST.LOGIN, <--- ROOT_AE_P_PST_E AS PST
PRO.SHOP_PERSON <--- qrySHOP As qS

Start a new query and add
qryProposalsOpenedBackOpen
qryShop
ROOT_AE_P_PST_E

Right-mouse click on the table qryProposalsOpenedBackOpen,
choose "Properties,"
and set "Alias" to "qP"

Right-mouse click on the table qrySHOP,
choose "Properties,"
and set "Alias" to "qS"

Right-mouse click on the table ROOT_AE_P_PST_E,
choose "Properties,"
and set "Alias" to "PST"

Join qP.PROPOSAL to qS.PROPOSAL

Join qP.PROPOSAL to PST.PROPOSAL

Double-click on the fields in the tables that you want
(as referenced above) to send them down to the grid.

Save the query.

We've indexed, divided-and-conquered, and sliced-and-diced....

If that does not help (and if I did not misunderstand),
I am out of ideas for the moment...
Click to expand...
 
G

Gary Walter

Paul B said:
Gary, first off thanks for the help, I think I am getting in deep here,
not
to good with Access, the data is coming from a linked table from an ODBC
data base that I have no control over, just trying to get some data out of
it. I have a lot more experience with excel and have managed to import the
data into Excel and get the information I need with a few helper columns
and
some formulas, but I would like to keep it all in Access, if I can, so I
will try and follow the instructions you gave and see it I can get it to
work that way. It will be Monday before I can try it out at work.
Click to expand...

Are *all 3 tables* linked tables from an ODBC database?

If so...it may then help to just get simple data from them,
pump it into an Access table, then run a query on this table
to sort out "[25Date]>[70Date]."

Start with the following query:

SELECT
PHS.PROPOSAL,
PHS.SHOP,
PST.STATUS_CODE,
PST.STATUS_DATE,
PST.LOGIN,
PRO.SHOP_PERSON
FROM
(ROOT_AE_P_PHS_E AS PHS
INNER JOIN
ROOT_AE_P_PST_E AS PST
ON
PHS.PROPOSAL = PST.PROPOSAL)
INNER JOIN
ROOT_AE_P_PRO_S AS PRO
ON
(PHS.PROPOSAL = PRO.PROPOSAL)
AND
(PHS.SORT_CODE = PRO.SORT_CODE)
WHERE
(PHS.SHOP = "56")
AND
((PST.STATUS_CODE = "70-WRKCOMP")
OR
(PST.STATUS_CODE = "25-ASSGN"))
AND
(PST.STATUS_DATE Between Date()-30 And Date()+1);

How does that perform suitably?

If so, then change it into a make table query.
In top menu, click on Query/Make Table.
When it asks for table name, type in something
like "tblCurrentData"

Click on the red exclamation mark to run it.

Does that perform adequately?

If so, change it to an append query.

Save it as something like "qryapptblCurrentData"

Then, test "divide-and-conquer" on tblCurrentData.

qry25Date

SELECT
T.PROPOSAL,
Max(T.STATUS_DATE) As Max25Date
FROM
tblCurrentData As T
WHERE
T.STATUS_CODE = "25-ASSGN"
GROUP BY
T.PROPOSAL;

qry70Date

SELECT
T.PROPOSAL,
Max(T.STATUS_DATE) As Max70Date
FROM
tblCurrentData As T
WHERE
T.STATUS_CODE = "70-WRKCOMP"
GROUP BY
T.PROPOSAL;

qryProposalsOpenedBackOpen

SELECT
q1.PROPOSAL,
q1.Max25Date,
q2.Max70Date,
FROM
qry25Date As q1
INNER JOIN
qry70Date As q2
ON
q1.PROPOSAL = q2.PROPOSAL
WHERE
q1.[Max25Date] > q2.[Max70Date];

These should zip. All that is left is the final query...

Start a new query and add
qryProposalsOpenedBackOpen
tblCurrentData

Right-mouse click on the table qryProposalsOpenedBackOpen,
choose "Properties,"
and set "Alias" to "qP"

Right-mouse click on the table tblCurrentData,
choose "Properties,"
and set "Alias" to "T"

Join qP.PROPOSAL to T.PROPOSAL

Double-click on the fields in the tables that you want
to send them down to the grid.

Save the query.

//////////////////////////////////
In code you can rewrite the SQL of "qryapptblCurrentData"
for a different SHOP and/or time frame.

Then empty "tblCurrentData"

Run "qryapptblCurrentData"

Then open report based on your final query.

I can imagine a form where user sets SHOP and/or time frame.

A command button on the form does the above in its Click Event code.
 
P

Paul B

Gary, Yes all 3 tables are linked tables from an ODBC database, I will try
what you have posted on Monday, Thanks

--
Paul B

Gary Walter said:
Paul B said:
Gary, first off thanks for the help, I think I am getting in deep here,
not
to good with Access, the data is coming from a linked table from an ODBC
data base that I have no control over, just trying to get some data out of
it. I have a lot more experience with excel and have managed to import the
data into Excel and get the information I need with a few helper columns
and
some formulas, but I would like to keep it all in Access, if I can, so I
will try and follow the instructions you gave and see it I can get it to
work that way. It will be Monday before I can try it out at work.
Click to expand...

Are *all 3 tables* linked tables from an ODBC database?

If so...it may then help to just get simple data from them,
pump it into an Access table, then run a query on this table
to sort out "[25Date]>[70Date]."

Start with the following query:

SELECT
PHS.PROPOSAL,
PHS.SHOP,
PST.STATUS_CODE,
PST.STATUS_DATE,
PST.LOGIN,
PRO.SHOP_PERSON
FROM
(ROOT_AE_P_PHS_E AS PHS
INNER JOIN
ROOT_AE_P_PST_E AS PST
ON
PHS.PROPOSAL = PST.PROPOSAL)
INNER JOIN
ROOT_AE_P_PRO_S AS PRO
ON
(PHS.PROPOSAL = PRO.PROPOSAL)
AND
(PHS.SORT_CODE = PRO.SORT_CODE)
WHERE
(PHS.SHOP = "56")
AND
((PST.STATUS_CODE = "70-WRKCOMP")
OR
(PST.STATUS_CODE = "25-ASSGN"))
AND
(PST.STATUS_DATE Between Date()-30 And Date()+1);

How does that perform suitably?

If so, then change it into a make table query.
In top menu, click on Query/Make Table.
When it asks for table name, type in something
like "tblCurrentData"

Click on the red exclamation mark to run it.

Does that perform adequately?

If so, change it to an append query.

Save it as something like "qryapptblCurrentData"

Then, test "divide-and-conquer" on tblCurrentData.

qry25Date

SELECT
T.PROPOSAL,
Max(T.STATUS_DATE) As Max25Date
FROM
tblCurrentData As T
WHERE
T.STATUS_CODE = "25-ASSGN"
GROUP BY
T.PROPOSAL;

qry70Date

SELECT
T.PROPOSAL,
Max(T.STATUS_DATE) As Max70Date
FROM
tblCurrentData As T
WHERE
T.STATUS_CODE = "70-WRKCOMP"
GROUP BY
T.PROPOSAL;

qryProposalsOpenedBackOpen

SELECT
q1.PROPOSAL,
q1.Max25Date,
q2.Max70Date,
FROM
qry25Date As q1
INNER JOIN
qry70Date As q2
ON
q1.PROPOSAL = q2.PROPOSAL
WHERE
q1.[Max25Date] > q2.[Max70Date];

These should zip. All that is left is the final query...

Start a new query and add
qryProposalsOpenedBackOpen
tblCurrentData

Right-mouse click on the table qryProposalsOpenedBackOpen,
choose "Properties,"
and set "Alias" to "qP"

Right-mouse click on the table tblCurrentData,
choose "Properties,"
and set "Alias" to "T"

Join qP.PROPOSAL to T.PROPOSAL

Double-click on the fields in the tables that you want
to send them down to the grid.

Save the query.

//////////////////////////////////
In code you can rewrite the SQL of "qryapptblCurrentData"
for a different SHOP and/or time frame.

Then empty "tblCurrentData"

Run "qryapptblCurrentData"

Then open report based on your final query.

I can imagine a form where user sets SHOP and/or time frame.

A command button on the form does the above in its Click Event code.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with QFE Q824704 2
who can help please with table query ? 1
Repeated failures trying to burn audio CD -- help needed 2
Outlook crashes with Outlook connector 2002 0

Top