Terence-
No. I merely assigned an alias to the table within this query. I did that in
the original example because the table name you gave me was long, and I'm lazy -
preferred to type "TA" instead of "Student_whatever".
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
Sent: Wednesday, August 24, 2011 1:37 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: query assistance, possible join
You are coorect John, but I still need/want more explanation if you plz,
The below gets me the 2 fields from student withou multiples coorect ?
SELECT DISTINCTROW Student.student_name, student.student_type
FROM student
Now here you are creating a temporrary table (TA) and including only the records
who match the join fields and meet the date criteria ?
is that correct ?
Terence
INNER JOIN DC AS TA ON student.student_name=TA.DC_stu
WHERE
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Terence-
>
> If what you told me is correct about the field names, you need DC_Stu, not
> DC_Student_Name.
>
> SELECT DISTINCTROW Student.student_name, student.student_type
> FROM student INNER JOIN DC AS TA ON student.student_name=TA.DC_stu
> WHERE dc.dc_dav>=#7/1/2010# And DC.dc_dav<#7/1/2011#;
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> Sent: Wednesday, August 24, 2011 12:54 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: query assistance, possible join
>
>
>
> ok john , I tried the following
>
> SELECT DISTINCTROW Student.student_name, student.student_type
> FROM student INNER JOIN DC AS TA ON student.student_name=TA.DC_student_name
> WHERE dc.dc_dav>=#7/1/2010# And DC.dc_dav<#7/1/2011#;
>
> my real tables are named DC with fields DC_stu and DC_dav
> other table is
> Student with field student_name and student_type
>
> I took your first example, and typed it into the SQL box, changing the table
> names and field names to match accordingly. I got no syntax error, but, when I
> try to leave SQL mode and goto design mode access tells me that IT cant
> represent the Join expression in design mode.
>
> ??? I assume its becasue we are creating a new table as TA ?
> terence
> I will try the second example you gave
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Terence-
> >
> > Class is in session!
> >
> > SELECT DISTINCTROW Stu_Table.Stu_name, Stu_Table.Stu_Discipline
> > FROM Stu_Table
> > INNER JOIN Attend_Table As TA
> > ON Stu_Table.Stu_Name = TA.Attend_Student_Name
> > WHERE Attend_Table.Attend_Date >= #7/1/2010#
> > AND Attend_Table.Attend_Date < #7/1/2011#;
> >
> > Or:
> >
> > SELECT Stu_Table.Stu_Name, Stu_Table.Stu_Discipline
> > FROM Stu_Table
> > WHERE Stu_Table.Stu_Name IN
> > (SELECT Attend_Table.Attend_Student_Name
> > FROM Attend_Table
> > WHERE Attend_Table.Attend_Date >= #7/1/2010#
> > AND Attend_Table.Attend_Date < #7/1/2011#)
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> > Sent: Wednesday, August 24, 2011 9:52 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] query assistance, possible join
> >
> > I have several ways to accomplish my task, I am interested to see how others
> > would approach this simple task. I have two tables as follows:
> >
> > Attend_Table Stu_Table
> >
> > Attend_Date Stu_name
> > Attend_student_name Stu_Discipline
> >
> > The Stu_Table will have only 1 record per Stu_name. The Attend_Table may
have
> > the the same many records with the same student name ( Attend_student_name
).
> >
> > The tables are not related except by virtue of the Attend_student_name
> matching
> > to Stu_name.
> >
> > The task is to determine All Stu_name's and Stu_Discipline's that attended (
> > i.e. have a record in the attend_table) between 7/1/2010 ans 6/30/21).
> > Additionally I dont want the query to result in duplicate student names.
> >
> > SO..If a Stu_name matches Attend_student_name and that record has a
> Attend_date
> > within the date criteria, then report that student's name and their
Discipline
> > ONCE.
> >
> > I thought I could use an outer Join to accompish this but outer joins seem
to
> > report ALL records and simply add nulls to non mathed fields.
> >
> > I beleive I can do it with 2 query's as follows:
> >
> > Query1
> > [Attend_table]![Attend_Date] => #7/1/2010# and [Attend_table] <= #6/30/2011
> >
> > Query2 ( Uses Query1 as Source )
> > SELECT DISTINCT Query1.student_name
> > FROM Query1
> >
> > Any ther clever Ideas, or perhaps how to do this with a Join statement?
> >
> > This is NOT an Urgent question, this is more for my own Edumacation :)
> > Terence
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar