consultation with field for multiple criteria

[fabiobarreto10]

Gentlemen,



I have a sub form with the following fields:name of the step,the scheduled date,place and date rescheduled date.I'm having trouble making a statement in a query
that has the following criteria:need to know what the step is smaller than today's date between the date scheduled and rescheduled date and the date performed without value.
if there are any other dates scheduled and rescheduled less than today,I need to know only the name is the first step with the date due.
I think I need to create a field in the query to the term, but I'm not getting.Any help is welcome.Thank you.

[June7]

Not sure I understand your criteria. Verify and explain further:

1. step is smaller than ?

2. today's date between the date scheduled and rescheduled date

3. date performed without value

4. I am totally lost on this "any other dates scheduled and rescheduled less than today,I need to know only the name is the first step with the date due"

[fabiobarreto10]

Thank you for responding, for help. I'm trying to make an appointment to make a report
by each assessor.
I have a form where there is a comboBox with the names of advisors. After selecting a name, I'm trying to
open a report with all the companies and products, and won every step of their product. the difficulty
I'm finding is this: for example, may have one or more steps losers, I just need to know
first step is unsuccessful, according to each product and their company's advisor.
I need to have a field in the report (or query, then use the report), with the date of the step
is unsuccessful, the date can be scheduled or rescheduled date. If the date field is populated place, the
step is ok. Sorry for not having prepared better. I am sent images that may facilitate the understanding of
my problem.
http://dl.dropbox.com/u/53793430/Desktop.zip
Again thank you very much.

[June7]

Which form and which report in the database do I look at for this issue?

You want the report to look like the spreadsheet?

[fabiobarreto10]

the name of the form I'm trying to open the report called FormulárioTesteRelatório, (the report is a model
based on consultation, but did not work). Perhaps it would be more practical to place the button to open the report in the form fFiltros,
when updating the name of the assessor. the user selects the button and open the report.
Yes, I'm trying to get the report as the template excel file.
Thank you.

[June7]

So the report does not exist?

Then what tables would be involved in making the report?

[fabiobarreto10]

Yes, I was trying to do the query in order to make the report.

The tables are: TblAnalista, TblEmpresa, and TblProduto TblPassosStatus.
obs. only enabled products.

[June7]

TblProduto has two Yes/No fields: Ativado and Reativado. This should be one Yes/No field. A record is either active or not active (yes or no). So check box is either checked or not checked (yes or no). Criteria would be either Yes or No to filter records on this field.

The spreadsheet doesn't show any data from TblAnalista. What table and field does the Counter value come from?

[fabiobarreto10]

Sorry, I will elaborate on.

reactivated the field should be ignored, I use this field only to help, to display a text in the form
for when a product is reactivated.

My query, as well as in the report, need to know the steps of delay only active products, ie with the value -1.


the bank has a form called FormulárioTesteRelatório for when selecting an adviser show the report
companies and products of their advisor (TblAnalista> NomeDoAnalista (Name of analyst) = adviser (Assessor)
the report I am trying to show the name of the officer, just to make the report clearer. In the spreadsheet model
ADVISORY has a field called to display the name of the adviser.

the field counter is in accordance with each step, ie, each step may be the counter with the value 0, 1 ...

Sorry, the model looked like it's a step count, but it is actually a number of changes that were made on the rescheduled date.

[June7]

Okay, will ignore Reativado.

Now I see the assessor name in the spreadsheet.

Still don't know what field the counter is from.

[fabiobarreto10]

yes, I will elaborate on the field counter.

the field counter (C) TblPassosStatus table, is a field where every time the rescheduled date field (DataReprogramada) is changed or added the first time, add 1 to counter. In the query or report I would know which step is in arrears, on which date (scheduled or rescheduled) and number of times that the rescheduled date field has changed.

[June7]

Okay, now clarify criteria.

1. Include records where Ativado is yes?
2. Include records without Realizada date?
3. Include records where current date later than Programada/Reprogramada?

Does this query get close:
SELECT TblAnalista.NomeDoAnalista, TblEmpresa.NomeDaEmpresa, TblProduto.NomeDoProduto, TblPassosStatus.Passo, TblPassosStatus.DataProgramada, TblPassosStatus.DataReprogramada, Nz([DataReprogramada],[DataProgramada]) AS DataDue, TblPassosStatus.DataRealizada, TblPassosStatus.C, TblProduto.Ativado
FROM ((TblAnalista LEFT JOIN TblEmpresa ON TblAnalista.CodAnalista = TblEmpresa.CodAnalista) LEFT JOIN TblProduto ON TblEmpresa.CodEmpresa = TblProduto.CodEmpresa) LEFT JOIN TblPassosStatus ON TblProduto.CodProduto = TblPassosStatus.CodProduto
WHERE (((Nz([DataReprogramada],[DataProgramada]))<Date()) AND ((TblPassosStatus.DataRealizada) Is Null) AND ((TblProduto.Ativado)=Yes));

[fabiobarreto10]

ok, thank you for the SQL statement, it worked perfectly, I'm just trying to show only the first step in arrears.
for example, the product has the following steps SoluçãoY arrears: faspre, cadastrobásico, enviodeinformações, indetificarinteressados ​​and scheduling.
this case the query I'm trying to show only the fans step before (FASPRÉ), which is the first step that is overdue.

I'm also trying to DAtaProgramada fields, DAtaReprogramada, DataRealizada, and Ativado On are hidden.

[June7]

Whenever you want to show a record selected by result of aggregate calculation, in this case it will be Min, a subquery or domain aggregate (such as DMin) is needed. Following is query approach.

Use the first query to build another:
SELECT Query1.NomeDoAnalista, Query1.NomeDaEmpresa, Query1.NomeDoProduto, CDate(Min(Query1.DataProgramada)) AS MinOfDataProgramada
FROM Query1
GROUP BY Query1.NomeDoAnalista, Query1.NomeDaEmpresa, Query1.NomeDoProduto;

Now join that query back to Query1:
SELECT Query1.NomeDoAnalista, Query1.NomeDaEmpresa, Query1.NomeDoProduto, Query1.Passo, Query1.DataDue
FROM Query2 INNER JOIN Query1 ON (Query2.MinOfDataProgramada = Query1.DataDue) AND (Query2.NomeDoProduto = Query1.NomeDoProduto) AND (Query2.NomeDaEmpresa = Query1.NomeDaEmpresa) AND (Query2.NomeDoAnalista = Query1.NomeDoAnalista);

The result is that every record returned has FAS Pré passo with the earliest unsuccessful due date. Is that the output you expected?

[fabiobarreto10]

Thank you. partially worked, for example:

the company Empresateste2, produtoC. faspré step is unsuccessful with the scheduled date 08/02/2012, the query shows ok. but when you add a date field
rescheduled date, (DataReprogramada) and this date is also unsuccessful (for example, placed 09/02/2012) no longer shows the query step. In this case, it would appear that the step faspre and
field DataDue the new date (09.02.2012).

I tried to add the Counter field (C) in the other two queries, but did not work returned the following message:

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.