Add checkboxes to query

Michael Noze
Michael Noze used Ask the Experts™
on
Hi Experts,

I'm looking for a way to add a checkboxes column to a query and it would be used in a form.
My data come from a linked Excel file.

Is there a way to do it?

Thank you.
Database2.accdb
DataSAP.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
For your case the easiest way would be to create a temp table with the structure of your Linked Excel table and just append the data ...the temp table also holds a yes/No field
check my attachment (Link the Excel)
Database2.accdb

Author

Commented:
Hi John Tsioumpris,
I just have an issue with your solution, how can I update the temp table in Access without adding existing data into it with an updated Excel?

What I've done is I linked my Excel file, added and removed some data in it and I ran the append query.
It added to the existing temp table all the new data.



Michael
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
First, to hold the data for the form, create a table, tblPlanningData, having the additional field for the checkbox.
Bind the form to this table.

Next, create a Combined "update or Append" query to read in the data from the attached Excel file:

UPDATE 
    tblData 
LEFT JOIN 
    tblPlanningData 
    ON tblData.[Order number] = tblPlanningData.[Order number] 
SET 
    tblPlanningData.[Order number] = [tblData]![Order number], 
    tblPlanningData.[Date] = [tblData]![Date], 
    tblPlanningData.Description = [tblData]![Description];

Open in new window

Now, run this query whenever the Excel file has been updated.

See the attached demo, please.
Database2.accdb
John TsioumprisSoftware & Systems Engineer

Commented:
You can clear the temp table before any appending operation

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial