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?
Can anyone give me some pointers on this?
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?
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?
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.
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.
ASKER
??
What's the question?
ASKER
I don't understand what you wrote... Are Select and From two different queries?
ASKER
I don't understand what you wrote... Are Select and From two different queries?
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.
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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.
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
Thanks,
Davisro@netins.net
You can simply add a yes/no field at your table and display it on your report!
HTH!
Roby