Avatar of caliguy78
caliguy78
 asked on

Hot Wheels Database

Hopefully someone here can point me in the right direction.  I currently have a Access 2000 database that keeps track of roughly 2000 hotwheels.  Every car has their own sku number on my tables.  I want to be able to create a report that will display car numbers 1-200 with a checkbox next to each number.  I would like a check placed in the boxes with the cars I have.  I tried to do this but it didn't work.

Can anyone give me some pointers on this?
Microsoft Access

Avatar of undefined
Last Comment
Rog D

8/22/2022 - Mon
rquaglia

HellO!
You can simply add a yes/no field at your table and display it on your report!

HTH!
Roby
caliguy78

ASKER
I don't think it is that simply...  Here is a scaled down version of what I am trying to do...
I have a table named 2003.  In that table I have a column named car# that is numbered 1-42.  I have a second table named collection that has several fields.  One of the fields is also car#.  Lets say I have cars 1-5 only.  I want to create a report that will list the numbers 1-42 in one column.  I then want the report to query my collection table.  For the six cars I have, I would like a checkbox included on the report.  That way, from one report, I can see the car numbers I have as well as the ones I don't have.  Does that make any sense?
mcallarse

Create the following query (myQuery):

SELECT
  2003.Car#, Collection.Car# Is Not Null AS GotIt
FROM
  2003 Left Join Collection on 2003.Car#=Collection.Car#

In your report, set myQuery to be the record source. Create a checkbox on the report, and set GotIt to be the control source.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
caliguy78

ASKER
??
mcallarse

What's the question?
caliguy78

ASKER
I don't understand what you wrote... Are Select and From two different queries?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
caliguy78

ASKER
I don't understand what you wrote... Are Select and From two different queries?
mcallarse

No. The line breaks and indentation are for readability. The query is:

SELECT 2003.Car#, Collection.Car# Is Not Null AS GotIt FROM 2003 Left Join Collection on 2003.Car#=Collection.Car#

GotIt is a calculated boolean value, true (-1) if there is a matching car in the collection table and false (0) if there is not. This field will serve as the control source of the checkbox, making it checked if a car exists, and unchecked if one does not.
caliguy78

ASKER
What fields do I use and where do I input this expression?  It keeps coming back as invalid syntax.  (I am not very good with queries)  Basically, if I am creating a new query from scratch what do I do step by step?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
caliguy78

ASKER
What fields do I use and where do I input this expression?  It keeps coming back as invalid syntax.  (I am not very good with queries)  Basically, if I am creating a new query from scratch what do I do step by step?
ASKER CERTIFIED SOLUTION
mcallarse

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
caliguy78

ASKER
I am still having a problem with this.  I create a query and I pasted that into the sql view.  The query saves with no errors, but when I run it, I am getting a type mismatch error.  What does that mean?
caliguy78

ASKER
Ok... I figured it out!.....  One of my table fields was in text format while the other was a number.  Something so simple threw me off.  
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rog D

I was just curious if you could possible share that database or just the table with me as I have several hotwheel cars I would like to record.

Thanks,

Davisro@netins.net