Need a query and report that finds "Yes" ticked in @30 Yes/No boxes

[bandit]

I have a table which has customer details and approximately 30 Yes/No type fields with a vegetable type in each. I need to be able to run a 'query' which will give me the customer name (first name, last name) and which vegetables they have ticked/checked as 'Yes' and ultimately produce a report with this information also



Probably very simple to many out there but I thank you in advance for your kind help

[rpeare]

So you're trying to find any record where any vegetable is checked with a yes/no?

This sounds like a non-normalized table structure but basically you could have a single expression that was something like

[veg_01] + [veg_02] + [Veg_03]... etc

Then in your criteria show any record where the value of this sum was less than zero (yes/no fields are stored as a -1 for yes, 0 for no)

[bandit]

Thanks for your reply. In a nutshell I have a veggiebox business and the customers can opt out of receiving certain veg. Originally I tried to have a relational database with a 'customer details' table and a 'vegetable' table and gave up trying to link them
So now I have one table with both customer details and vegetables in. So basically I check the yes/no box against whatever the customer has opted out of to record that fact but need to check each week what their optouts are. So I need to pull from the table a list of customers and the vegetables which I have checked as 'Yes'.......sorry hope that makes sense

[John_G]

With the data structure you have, this will very difficult. As rpeare pointed out above, your data is not properly normalized. The customer details should contain only one vegetable per record :

OrderID (which will give you customer ID)
VegetableID
...
... plus other fields, like quantity etc.

Then you need another table for Vegetables:

VegetableID
VegetableName
...

Once you do this, reports are easy.

John

[send2steph]

This sounds like a non-normalized table structure...

So, you want:

Name Veg1, Veg2, Veg5, Veg10
Name2 Veg3, Veg8, Veg30
etc?

I'm thinking your second column is going to be a bunch of IIf statements in your query.

IIf(Veg1=-1,"Veg1") & IIf(Veg2=-1,", Veg2") & IIf(Veg3=-1,", Veg3") etc...

[rpeare]

Exactly what John_G said, what you're trying to do is incredibly difficult with a non-normalized structure. How married are you to your current structure?

What you really should is a structure something like:

tblCustomers
Cust_ID  Cust_FN  Cust_LN ---> other customer related fields

tblVegetables
Veg_ID Veg_Name ----> other vegetable related fields

tblCustVeg
CV_ID Cust_ID  Veg_ID

The table tblCustVeg is called a junction table because it connects to different tables, in this case connecting your customers to the vegetables they have on their list

[bandit]

Okay I have created new Tables called
CustVeg with the fields ContactID (which is a field in my Customers Table) VegID and CustVegID

and a Table called Veg which has VegID VegName and NotWanted as its fields.

Could you please advise how these should now be linked relationship wise. ContactID in the Table Customers is an Autonumber but in CustVeg a Number

I have populated the Veg table with VegName the VegID is an Autonumber

many thanks

[rpeare]

your junction table should just include vegetables that the person WANTS not what they want to exclude, it will be easier for you in terms of programming/query building to make it inclusive rather than exclusive. If you build an table where you are tracking the vegetables they DO NOT want you would have to compare it to a list of all your vegetables to get the ones they wanted.

So if you were going to build a query that showed which vegetables the person wanted to see a list of each week (or month or year)

You'd add all three tables to your query link the ContactID from your Customer stable to the ContactID in your CustVeg table then link the VEGID from the CustVeg table to the Veg table so you so let's say your CustomerID 1 belonged to John Doe, in your custveg table you had him wanting information on vegetables (VegID's) 1, 3 and 5, in your Veg table you'd Items 1, 3 and 5 corresponded to Peas, Carrots and Corn respectively. Your query would show John Doe on three lines one line for each vegetable he wanted information on.