Query help please....

[sm100378]

Hi,

I have two tables in Access that I need to query.

One table has (student#, last, first, employer, ItemA, ItemB, Degree)-named
tblPeople

One table has (sutdent#,last, first, itemA, itemB)-named tblSpecial

-----> Item A, B are simply "special dates"

What I am trying to do is make a query that lists all tblPeople, but
subtracts people that are in tbleSpecial. tblPeple is my master table, and
tblSpecial is a table where people are in both tables.

I am wondering what verbage (command) to use after I am in design query and
drag tblpeople to the query setup, and then drag from tblSpecial as well in
the query setup (below the relationships).

tblpeople tblpeople tblpeople tblpeople tblSpecial tblSpecial tblspecial (to
give you an idea, they are just simply dragged to the creation of the query).

I have about 2000 people in tblPoeple and about 250 people in tblSpecial
(tblSpecial people are in tblPeople as well). and just need to tell it NOT
INCLUDE tblSpecial people.

Thank you very much!

 

[Karl Krelove]

Is there a reason why you have two tables that duplicate information? Could
you do the same thing more easily by adding a Boolean (Yes/No) field (call it
"Special" or whatever you you want) and check the field ("Yes") for anyone
you're now including in tblSpecial. Then the query is simple using only the
single table tblPeople - just specify that the Special field have a value of
No.

Karl Krelove

 

[CompGeek78]

The other poster is 100% correct, this is a non-normalized table setup,
which is why you are having trouble making it work. However, if you
need to get the results without changing your table structure, make a
query using the following SQL statement.

SELECT tblPeople.*
FROM tblPeople LEFT JOIN tblSpecial ON tblPeople.Student# =
tblSpecial.Student#
WHERE (((tblSpecial.Student#) Is Null));

You would be better off normalizing the tables in the long run, but
this is a short term solution.

Keven

 

[sm100378]

Hi,

So I pull the required info from the single table showing in the
relationships above. They are all listed normally. Now I create another
field (named Special)and then under that click "NO". Then in critera I enter
"no" and that will automatically tell the query to list ALL people but NOT
include people with the itemA and itemB dates?

 

[sm100378]

Just to summarize, and I so much appreciate the help!

The tbl people is master and houses ALL students. Also, some of the
tblPeople have ItemA and some dont, depending on if they need a fax on file
(letting me know the date). tbl People contains people with zero, one or
both itemA, itemB.

The tbl special is another table that (to make this make more sense) has
students who have special dates filled into the forms that represent
paperwork received. These people are ALSO in tblPeople.

My hope is to have the "new table" house ALL students that do not have both
itemA and itemB (in otherwords tblSpecial has these two itemA and itemB
filled in).

I am learning access everyday and dont know SQL yet, so I dont know where to
input the language.


Please advise

 

[Karl Krelove]

Almost. You need to add the Yes/No field to tblPeople.
- Go to the design view of the table tblPeople (not your query) and add a
Yes/No field at the end of the list of fields and call it Special.
- Close the design view and allow Access to save the changes to the table
- Open tblPeople in data view and click on the checkbox for each record
(each student) you want to mark as "Special" (the ones who are now in
tblSpecial) - you could also do this in the query after it's set up.
- Open your query in design view and add tblPeople to it.
- Set the query up to display all the fields you want to view. Include the
"Special" field at the end and if you don't want to display it, uncheck the
box in the Show row.
- Set a criterion under the Special field as "No"

The resulting display will show only those records that aren't check-marked
as Special. You can also do it the other way - if you want to see only the
Special students, use a criterion of "Yes". If you want to see all the
students, don't put any criterion in the space under Special.

If you do it this way, you only need to enter the data for each student
once, not twice. Once you delete the second table and compact the database,
your mdb file will be a little smaller into the bargain.

Karl

 

[CompGeek78]

I am learning access everyday and dont know SQL yet, so I dont know where to input the language.

In order to input the query above, create a new query in design view,
go to the View menu and choose SQL view. Then, you can just copy and
paste the SQL statement from above into that window.

I would highly recommend following the information from the other
poster about normalizing this data, but as I said, this will give you a
quick result.

Keven