Help with query, Multiple yes no

[peterod]

I have a database which has a yes no in each of the tables, set manually to true by the user.

I want to query the database to return the fields with the check box true.

I am unsure how to do this. I can get it to do it no problem on 1 checkbox....

Please see below a diagram of how I have tried to do this.

I should note that only some of the records in each table will be checked.

Here is the SQL

SELECT Tasks.Title, Tasks.[GFD Project Number], Tasks_Concept.[Concept TBC], Tasks_Concept.[Concept Time Taken], Tasks_Concept_rev.[To be charged], Tasks_Concept_rev.[Time Taken], [Tasks_Initialmeet&Quote].[Initial Meeting TBC], [Tasks_Initialmeet&Quote].[Initial Meeting Time Taken], [Tasks_Initialmeet&Quote].[Quote time TBC], [Tasks_Initialmeet&Quote].[QuoteTime Taken], [Tasks_Initialmeet&Quote].[Further Meeting TBC], [Tasks_Initialmeet&Quote].[Further Meeting TBC], Tasks_Outlinepl.[Outline Planning TBC], Tasks_Outlinepl_rev.[Time Taken], Tasks_Outlinepl_rev.[To be charged], Tasks_Planning.[Planning Time Taken], Tasks_Planning.[Planning TBC], Tasks_Planning_rev.[Time Taken], Tasks_Planning_rev.[To be charged], Tasks_Preapp.[Pre App Meeting Time Taken], Tasks_Preapp.[Pre App Meeting TBC], Tasks_Preapp_rev.[Time Taken], Tasks_Preapp_rev.[To be charged], Tasks_Timberframe.[Timberframe Taken], Tasks_Timberframe.[Timberframe TBC], Tasks_Warrant.[Warrant TBC], Tasks_Warrant.[Warrant TBC]
FROM ((((((((((Tasks INNER JOIN Tasks_Concept ON Tasks.[ID] = Tasks_Concept.[Taskid]) INNER JOIN Tasks_Concept_rev ON Tasks.ID = Tasks_Concept_rev.TaskID) INNER JOIN [Tasks_Initialmeet&Quote] ON Tasks.ID = [Tasks_Initialmeet&Quote].TaskID) INNER JOIN Tasks_Outlinepl ON Tasks.ID = Tasks_Outlinepl.TaskID) INNER JOIN Tasks_Outlinepl_rev ON Tasks.ID = Tasks_Outlinepl_rev.TaskID) INNER JOIN Tasks_Planning ON Tasks.ID = Tasks_Planning.TasksID) INNER JOIN Tasks_Planning_rev ON Tasks.ID = Tasks_Planning_rev.TaskID) INNER JOIN Tasks_Preapp ON Tasks.ID = Tasks_Preapp.Taskid) INNER JOIN Tasks_Preapp_rev ON Tasks.ID = Tasks_Preapp_rev.TaskID) INNER JOIN Tasks_Timberframe ON Tasks.ID = Tasks_Timberframe.TaskID) INNER JOIN Tasks_Warrant ON Tasks.ID = Tasks_Warrant.Taskid
WHERE (((Tasks_Concept.[Concept TBC])=True) AND ((Tasks_Concept_rev.[To be charged])=True) AND (([Tasks_Initialmeet&Quote].[Initial Meeting TBC])=True) AND (([Tasks_Initialmeet&Quote].[Quote time TBC])=True) AND (([Tasks_Initialmeet&Quote].[Further Meeting TBC])=True) AND ((Tasks_Outlinepl.[Outline Planning TBC])=True) AND ((Tasks_Outlinepl_rev.[To be charged])=True) AND ((Tasks_Planning.[Planning TBC])=True) AND ((Tasks_Planning_rev.[To be charged])=True) AND ((Tasks_Preapp.[Pre App Meeting TBC])=True) AND ((Tasks_Preapp_rev.[To be charged])=True) AND ((Tasks_Timberframe.[Timberframe TBC])=True) AND ((Tasks_Warrant.[Warrant TBC])=True));


Any help would be appreciated.

 

[Galaxiom]

Sorry to be the bearer of bad news but one glance at that mess tells me you need to rethink the whole data structure.

Many those different tables should be condensed into one table with a field to designate the stage of the negotiation.

 

[peterod]

Hi. thanks for the reply.

I know that it doesn't fully follow normalization rules, but the data input forms require it to be set out this way and it works for the purpose required...

Any ideas as to the query issue?

Imagine the tables were normalized and I had several check boxes across the tables.

The database front end is basically tabbed forms with sub forms for each stage.

 

[Galaxiom]

Imagine the tables were normalized and I had several check boxes across the tables.

Normalization is not some kind of arbitrary ideal that developers aspire to fulfil their sense of aesthetics. Normalization facilitates the ability to manipulate the data efficiently and effectively.

I want to query the database to return the fields with the check box true.

Queries are designed to return records. If the value in the field of the record is True you get the fields you asked for in that record and the related records.

I think you can get what you want by placing each of the True criteria in the query design grid on different rows. Placing them all on the same row means they all must be true to return the record.

However querying your data structure will continue to be incredibly cumbersome because the data model is horrible.

I suspect most of your related tables could be combined into one table with a field to indicate the single aspect of the record that you are currently using to place the record into a different table.

One clear normalization issue it the inclusion of an, (presumably boolean), Invoiced field and an InvoiceNumber field. Your record can indicate Not Invoiced yet include an Invoice Number. This is a sure sign of a normalization error.

Proper normalization relies on the presence of an Invoice Number to indicate Incoiced. You should be using Is Null on that field.

 

[peterod]

Would anyone be interested in advising on the database structure if I attached the database?

Can I attach the database?

It was based originally on a MS template file named tasks and is growing and getting quite messy.

I have a format for the forms/sub-forms that works at the moment and allows the user to see the relevant data assigned to each task quite quickly but, probably because of the data structure I am having big issues with the query's.

I only got access a few weeks ago and before that I really only had limited experience with php/mySQl

 

[Thales750]

How did you get that jpg image to show up on this site?

Thanks,

 

[peterod]

Ok.

Going on above i have further normalised my data structure which has simplified things immensely. As i am sure you would have expected.

I know that this is the Queries subforum so I will make a new thread for my question here.