Help! With query with multiple checkboxes

mansonpj

Registered User.
Local time
Today, 10:43
Joined
Nov 5, 2003
Messages
18
Can somebody help me.
I have created a query with the following SQL Statement.

SELECT Logbook.Platform, Logbook.Date, Logbook.Shift, Logbook.Discipline, Logbook.[Job Description], Logbook.Tag, Logbook.[Job Location], Logbook.Elec, Logbook.Inst, Logbook.Mech, Logbook.Prod, Logbook.Telecomms, Logbook.CRT, Logbook.HSEC, Logbook.[Maint Supv], Logbook.[Prod Supv], Logbook.Wglits, Logbook.WGE, Logbook.OIE, Logbook.[Deck Co]
FROM Logbook
WHERE (((Logbook.Platform)=[Forms]![DailyshiftReport]![Platform]) AND ((Logbook.Date)=[Forms]![DailyshiftReport]![shiftdate]) AND ((Logbook.Shift)=[Forms]![DailyshiftReport]![Shift]) AND ((Logbook.Discipline)=[Forms]![DailyshiftReport]![Discipline]))
ORDER BY Logbook.Date DESC;

What i need is to be able to search for information with regards to a date, discipline, but also need to pick up any information for the checkbox for that discipline aswell.

The db allows people to input data what ever discipline the are, but also tick checkboxes to allow other disciplines to now about a task carried out. The query then runs from a form and each daily handover report should incorporate what ever each disipline has put into the db plus if there are any other jobs they should know about.

Help Please!!!
 
You have a fundamental design flaw in your database and should normalise it to produce more efficient results.

By having a repeating group in your table you are violating Normal Form.
 
Don't understand

Can you explain, i have just started using access.

Paul
 
I was hoping you would take it into your own hands and go off searching for the word in bold. Alas...:rolleyes:

When a table finds that it has something repeating over and over i.e. Option1, Option2, Option3 then this is an indication that it deserves a table on its own.

A field in a table must be dependant upon the primary key.

If we have an Employees table then we can have fields called EmployeeID, Forename, Surname, EmailAddress and then you might think to put Department in also but the Department does not depend on the Employee so it does not go in that table, it goes in a table of its own.

Similarly, with Option1, Option2, Option3, ...Optionn these are an object independant of the table they are currently in.

A database should be growing downwards, not outwards. So, with your example where you have a checkbox for different disciplines, you might in the future have a new discipline to add which would mean changing the table design, then changing the query design and changing the form design and changing the report design, etc. whereas a new discipline table where you add the new discipline as a new record and not a new field is all that would be required and the database would happily accept it and there would be no need for changing any tables, forms,, reports, queries, etc.

Here's a couple of links:

Normalisation Basics

and

get the download on Understanding Relational Database Design.
 

Users who are viewing this thread

Back
Top Bottom