Show/Hide Fields based on criteria.

[jtm013]

Hello,

My DB houses job records with a due date for each phase of the job.
I am trying to make a query that will show which jobs are overdue and at which phase they are running late.



Right now I have the query set up like so:

Field:	Record Number	Customer Name	Phase 1 Due	Phase 1 Complete	Phase 2 Due	Phase 2 Complete	Phase 3 Due	Phase 3 Complete
Table:	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry
Sort:	Ascending	Ascending
Show:	Check	Check	Check	Check	Check	Check	Check	Check
Criteria:			<Date()	Is Null
Or:					<Date()	Is Null
							<Date()	Is Null

The problem I have is that there are actually about 12 phases and the output of the above shows all of the phases for each overdue record.
I am wondering if there is a way to have the query only show the phase which is over due.

Thank you for any and all assistance.

JT

[June7]

You mean conditionally show fields in query? No. Field is in the query or it isn't.

Could use VBA to manipulate QueryDefs but really don't advise that.

[ssanfu]

You *might* be able to use union queries, but I think 12 queries might be stretching it a bit.

The problem is your table is not normalized. It is violating 1st normal form because it has repeating groups.


A better structure would be:

Tbl_Data_Entry
=========================
Field Name................ field type
--------------------------------
RecordID ................ Autonumber
Record_Number....... whatever your requirements are (note - no spaces)
CustomerName ..... Long (FK to customer table)
PhaseDue .......... Date/Time (???)
PhaseComplete ..... Date/Time (???)
PhaseNumber ...... Integer
.
.

other fields...

my $0.02........

[jtm013]

@ ssanfu

Believe me the table has a number of issues, unfortunately my bosses have only given me 2 weeks to work through all the changes they want.

Would your suggestion be changed any if I told you that instead of Phase 1, 2 etc. the phases have actual names. (Ex: Phase 1 might be "R & D", Phase 2 might be "Planning" etc.)

[June7]

I agree with Steve about normalization and the field names makes no difference. A query could do a calculation that shows the first phase that is overdue. Try:

SELECT RecordNumber, CustomerName, Switch([Phase1Due]<Date() And IsNull([Phase1Completed]),"Phase1", [Phase2Due]<Date() And IsNull([Phase2Completed]),"Phase2", {continue for other 10 phases}) AS PhaseOverdue FROM tablename;

However, with 12 fields the expression might hit length limit and then will need a VBA custom function.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[ssanfu]

2 Weeks??????

If the query June suggests doesn't work, as a last resort - short of redesign - would be to create a table named something like "Tbl_TempPhase".
Then, using VBA, open your query and write only the data that meets your criteria for the report. The report would be based on a query on the temp table.


So in VBA,

1) Execute a delete query to clear the temp table
2) Loop through the recordset (of the query) processing each record, writing the data matching the criteria.
3) Then Open/Print Preview/Print the report

The query in step 2 would be like what you posted in #1, but without the criteria.

SELECT RecordNumber, CustomerName, [Phase1Due], [Phase1Completed],..., [Phase12Due], [Phase12Completed]
FROM Tbl_Data_Entry
WHERE .....(limit the records returned to the relevant ones)

It sounds like data has been used for field names. Definitely needs a redesign..




Remember........ it is only a phase.......

[jtm013]

Thanks for the help guys.

So far no luck though.
Right now my big headache is we switched our standard entry form to one which requires about twice as much data per record.
This will be useful in the future but it screwed up every query we had (just about) as every record prior to the switch does not include this data. So this problem just got back burner-ed.

Thanks again for all the assistance!