consultation with field for multiple criteria

[June7]

Are you reading dates as dd/mm/yyyy? I.e. 9/2/2012 is Sept 2, 2012? The table shows as mm/dd/yyyy so the date is Feb 9, 2012.



I had no problem including field C in queries 1 a 3. Don't want it in query 2.

Slight error in queries 2 and 3.

Query 2:
SELECT Query1.NomeDoAnalista, Query1.NomeDaEmpresa, Query1.NomeDoProduto, Min(Query1.DataDue) AS MinOfDataDue
FROM Query1
GROUP BY Query1.NomeDoAnalista, Query1.NomeDaEmpresa, Query1.NomeDoProduto;

Query 3:
SELECT Query1.NomeDoAnalista, Query1.NomeDaEmpresa, Query1.NomeDoProduto, Query1.NomeDoPasso, Query1.C, Query1.DataDue
FROM Query2 INNER JOIN Query1 ON (Query2.MinOfDataDue = Query1.DataDue) AND (Query2.NomeDoProduto = Query1.NomeDoProduto) AND (Query2.NomeDaEmpresa = Query1.NomeDaEmpresa) AND (Query2.NomeDoAnalista = Query1.NomeDoAnalista);

I was using Min on wrong field.

[fabiobarreto10]

Yes, I'm reading in the format dd / mm / yyyy.

Now solve a problem, but again the above, for example:


Turning in the previous example. The company Empresateste2, produtoC. fas step is pre programmed with the date 02.08.2012 unsuccessful, the query shows ok.
and now, when you add a rescheduled date in the date field, and this date is also unsuccessful (for example, placed 09/02/2012)
ok, the query now shows the step and date that is unsuccessful, now in the court date rescheduled (DataReprogramada).

Now in this case shows the step faspré perfectly. but also shows another step that is due CadastroBásico, I am
trying to show only the first step that has expired. in this case the step FasPré.


The field counter (C) is perfect.

[June7]

The results are showing the first unsuccessful step for each product for each company for each assessor. This agrees with the example data on the spreadsheet.

[fabiobarreto10]

Yes, thank you. but for example is a case that the date unsuccessful produtoC step FAsPré is the same date
the next step and query shows the two steps.


My also difficulty is that for example, a date reprogrammed (DataReprogramada) of a step can is overcome with a date later than
the next step with a scheduled date (DAtaProgramada) that is an earlier date.

In this case the query is showing pace with the scheduled date (DataProgramada) older but I'm actually trying to show only the date expired
and the first step is to overcome, even if it is a date later than the next step.

Just for clarity. please do the following example. A register in the new product company EMPRESATESTE2 (ProdutoJ, for example),
the first step called ContatoPrévio, DataRealizada field enter the date 08/02/2012.

In the second step FasPré enter the field and field DataProgramada 09/02/2012 15/02/2012 DAtaReprogramada.

In the third step CadastroBásico DataProgramada 10/02/2012 Type field.

The query will show step cadastroBásico with the date 10/02/2012, but I'm trying to show the previous step, in this case FasPré, even if the date
is later than the date that is the next step. in this case would have to query step FAsPré shows only the date 15.02.2012.

Just to be even clearer.
When the user enters a date in the field DAtaRealizada the first step
(ContatoPrévio), the field DAtaProgramada the next seven steps is completed
automatically with deadlines for the steps are performed.
But sometimes these steps are not performed on time.
so it has rescheduled the date field (DataReprogramada).
Therefore, time is a date unsuccessful earliest (older) in a next step
than one date unsuccessful in the previous step (in this case the date will be unsuccessful in step DAtaReprogramada).

Thank You.

[June7]

Are you saying should ignore DataReprogramada value as criteria?

[fabiobarreto10]

Sorry for being slow to respond, was having problems in my broadband.

I'll have to redo the automatic dates to no longer have the problem of having a date rescheduled
a step further than a new scheduled date of the next step.

Thus, the SQL queries that you sent me will work perfectly.

Thanks for the help and attention, I will mark as resolved.

Again, thank you.

[fabiobarreto10]

June7 I'm sorry to reopen this post.
I modified the fields with auto-dates in my subform and my problem was partially corrected.
But after the dates when due, I may have won more than one step. I tried to make an adaptation of the SQL statement
you sent me, but it did not work. I'm sending pictures with examples.
Note that a product can have more than one step delay. I am trying a query where I know the steps
in delays and their dates. unsuccessful if the date is the scheduled date field (DataProgramada) shows the date in this field.
If the date is expired the rescheduled date field (DataReprogramada) shows the date in this field.
Dates with orange colors, the pictures explain better.

Thank you.