[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

MS Access Add A Yes/No Box to Each Record

Thank you for looking at my question,

I want to create a table that takes the records from one query and adds a Yes/No box to each record.

I have two queries qryPriceData and qryYesNo and I have tried to combine the two as below but it returns zero records.

SELECT 
qryPriceData.Instrument, 
qryPriceData.Description, 
qryYesNo.YesNo

INTO tblInstrumentSelection

FROM qryPriceData, qryYesNo;

Open in new window


How should I do this please?
0
Crxfrd
Asked:
Crxfrd
1 Solution
 
PatHartmanCommented:
Does qryPriceData return records when you run it alone?  Adding the YesNo table as a Cartesian product would not change that.  However there are other ways to add a YesNo column.

1. You can include a dummy field but this won't make it a YesNo data type.  It will be an integer which will work just fine.  In fact I rarely use this data type since most of my data is kept in SQL Server.  You can always bind the field to a checkbox on a form or report to get the visual you want.
SELECT 
qryPriceData.Instrument, 
qryPriceData.Description, 
0 As YesNo

INTO tblInstrumentSelection

FROM qryPriceData;

Open in new window

2. If you prefer to have the additional column defined as Yes/No, then define the temp table and rather than using a make table query, use a delete query and an append query.
0
 
Dale FyeCommented:
I'm guessing that you want the ability to display this information and check the Yes/No either in a continuous form or datasheet, is that correct?

When I want to do this, I have a temp table in my database which contains a numeric ID type field, and a Yes/No field (IsChecked).  I first insert all of the ID field values from the table I want to "tag" with a Yes/No into that table, something like:

INSERT INTO TempYesNo(ID, IsChecked)
SELECT Instrument, 0
FROM qryPriceData

Then I create a query between that table and the other data using that primary key field as the join, allowing me to create an updateable query. that looks something like:

SELECT TempYesNo.IsChecked
, qryPriceData.Instrument
, qryPriceData.Description
FROM qryPriceData INNER JOIN TempYesNo on qryPriceData.Instrument = TempYesNo.ID


HTH
0
 
aikimarkCommented:
I use a make table query to add a boolean field, using the CBool() function in the query to create the Yes/No field.  Be sure to give your new column a name.
0
 
CrxfrdAuthor Commented:
Works perfectly, Thank you
0
 
Dale FyeCommented:
glad I could help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now