|
|
I have two tables that I need to create a mailing letter for. One of
the tables could have over 700 student records and the other table
could have 10,000 records a year depending on student grades.
Table 1
AutoID | StudentID | Last | First | Grade | YOG | SchoolYear
10001 | 9784 | Doe | Jane | 7 | 2013 | 08
10002 | 982745 | Doe | John | 6 | 2014 | 08
10003 | 26454565 | Doe | Jim | 8 | 2012 | 08
10004 | 234 | Doe | Bob | 7 | 2013 | 08
10006 | 234234 | Doe | Bill | 7 | 2013 | 08
Table 2
SubjectID | Subject | Average | CommentID | AutoIDf | EndOfWeek
24789 | Math | 59 | 1,4,2,5 | 10001 |
10/4/2008
24790 | Math | 43 | 1,4,2,5 | 10002 |
10/4/2008
24791 | Math | 22 | 1,4,2,5 | 10003 |
10/4/2008
24792 | PhysEd | 52 | 1,4,2,5 | 10001 |
10/4/2008
24793 | History | 61 | 1,4,2,5 | 10001 |
10/4/2008
24794 | Science| 32 | 5 | 10002 |
10/4/2008
24795 | Math | 49 | 4 | 10003 |
10/4/2008
24796 | Art | 58 | 1,5,3,4 | 10006 |
10/4/2008
24797 | English | 52 | 1, | 10006 |
10/4/2008
Now to the point. I want to make a table where the two are joined
like the following via a query though.
AutoID | StudentID | Grade | First | Last | Subject | Average |
CommentID | EndOfWeek
How can I go about doing this, as a single student could possibly have
10 failing grades in one week? I need to seperate it out this way so
I can push it into a word mailing to be mailed to the parents.
CommentID is not a typpo, it is just the way the dept requested it to
be.
Thanks in advance,
Jamie
|
|
Add both tables to the query
Clikc on AutoID in table 1 and drag to AutoIDF in table 2
Select the fields you want to display
Apply criteria to show only failing grades
Run the query.
If you want one record per student with failing subjects then you will
need to use a concatenate function. Google Duane Hookom + Concatenate
to find one you can download. I don't have the URL available.
Once you have downloaded the sample and copied the concatenate function
into a VBA module in YOUR database, you should be able to use a query
that looks something like the following
SELECT DISTINCT S.*
, Concatenate("SELECT DISTINCT Subject FROM [Table 2] as G1 WHERE
G1.Average <70 and G1.AutoIdf = " & S.AutoID) as FailingSubjects
FROM [Table 1] as S
WHERE S.AutoID in
(SELECT AutoIDF
FROM [Table 2] as G
WHERE G.Average < 70)
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
jsteenberge wrote:
[Quoted Text] > I have two tables that I need to create a mailing letter for. One of
> the tables could have over 700 student records and the other table
> could have 10,000 records a year depending on student grades.
>
> Table 1
>
> AutoID | StudentID | Last | First | Grade | YOG | SchoolYear
> 10001 | 9784 | Doe | Jane | 7 | 2013 | 08
> 10002 | 982745 | Doe | John | 6 | 2014 | 08
> 10003 | 26454565 | Doe | Jim | 8 | 2012 | 08
> 10004 | 234 | Doe | Bob | 7 | 2013 | 08
> 10006 | 234234 | Doe | Bill | 7 | 2013 | 08
>
> Table 2
>
> SubjectID | Subject | Average | CommentID | AutoIDf | EndOfWeek
> 24789 | Math | 59 | 1,4,2,5 | 10001 |
> 10/4/2008
> 24790 | Math | 43 | 1,4,2,5 | 10002 |
> 10/4/2008
> 24791 | Math | 22 | 1,4,2,5 | 10003 |
> 10/4/2008
> 24792 | PhysEd | 52 | 1,4,2,5 | 10001 |
> 10/4/2008
> 24793 | History | 61 | 1,4,2,5 | 10001 |
> 10/4/2008
> 24794 | Science| 32 | 5 | 10002 |
> 10/4/2008
> 24795 | Math | 49 | 4 | 10003 |
> 10/4/2008
> 24796 | Art | 58 | 1,5,3,4 | 10006 |
> 10/4/2008
> 24797 | English | 52 | 1, | 10006 |
> 10/4/2008
>
> Now to the point. I want to make a table where the two are joined
> like the following via a query though.
>
> AutoID | StudentID | Grade | First | Last | Subject | Average |
> CommentID | EndOfWeek
>
> How can I go about doing this, as a single student could possibly have
> 10 failing grades in one week? I need to seperate it out this way so
> I can push it into a word mailing to be mailed to the parents.
>
> CommentID is not a typpo, it is just the way the dept requested it to
> be.
>
> Thanks in advance,
>
> Jamie
|
|
I am now getting 'Compile error: User-defined type not defined'. I am
running Access 2007; will this cause the concatenate to not work?
Subjects: Concatenate("SELECT Subject FROM tblSubjects AS G1 WHERE
G1.AutoIdf = " & S.AutoID) FROM tblGuidance AS S WHERE S.AutoID in
(SELECT AutoIDf FROM tblSubjects AS G)
That is the line I created to just test the function to see if it will
work. I don't need to do a WHERE statement containing the average's
as this is only failing grades in the database. Do you happen to have
a IM program that we could possibly chat on?
Thanks for your help,
Jamie
On Dec 30, 5:16 pm, John Spencer <spen...[ at ]chpdm.umbc> wrote:
[Quoted Text] > Add both tables to the query
> Clikc on AutoID in table 1 and drag to AutoIDF in table 2
> Select the fields you want to display
> Apply criteria to show only failing grades
> Run the query.
>
> If you want one record per student with failing subjects then you will
> need to use a concatenate function. Google Duane Hookom + Concatenate
> to find one you can download. I don't have the URL available.
>
> Once you have downloaded the sample and copied the concatenate function
> into a VBA module in YOUR database, you should be able to use a query
> that looks something like the following
>
> SELECT DISTINCT S.*
> , Concatenate("SELECT DISTINCT Subject FROM [Table 2] as G1 WHERE
> G1.Average <70 and G1.AutoIdf = " & S.AutoID) as FailingSubjects
> FROM [Table 1] as S
> WHERE S.AutoID in
> (SELECT AutoIDF
> FROM [Table 2] as G
> WHERE G.Average < 70)
>
> '====================================================
> John Spencer
> Access MVP 2002-2005, 2007-2008
> The Hilltop Institute
> University of Maryland Baltimore County
> '====================================================
>
> jsteenberge wrote:
> > I have two tables that I need to create a mailing letter for. One of
> > the tables could have over 700 student records and the other table
> > could have 10,000 records a year depending on student grades.
>
> > Table 1
>
> > AutoID | StudentID | Last | First | Grade | YOG | SchoolYear
> > 10001 | 9784 | Doe | Jane | 7 | 2013 | 08
> > 10002 | 982745 | Doe | John | 6 | 2014 | 08
> > 10003 | 26454565 | Doe | Jim | 8 | 2012 | 08
> > 10004 | 234 | Doe | Bob | 7 | 2013 | 08
> > 10006 | 234234 | Doe | Bill | 7 | 2013 | 08
>
> > Table 2
>
> > SubjectID | Subject | Average | CommentID | AutoIDf | EndOfWeek
> > 24789 | Math | 59 | 1,4,2,5 | 10001 |
> > 10/4/2008
> > 24790 | Math | 43 | 1,4,2,5 | 10002 |
> > 10/4/2008
> > 24791 | Math | 22 | 1,4,2,5 | 10003 |
> > 10/4/2008
> > 24792 | PhysEd | 52 | 1,4,2,5 | 10001 |
> > 10/4/2008
> > 24793 | History | 61 | 1,4,2,5 | 10001 |
> > 10/4/2008
> > 24794 | Science| 32 | 5 | 10002 |
> > 10/4/2008
> > 24795 | Math | 49 | 4 | 10003 |
> > 10/4/2008
> > 24796 | Art | 58 | 1,5,3,4 | 10006 |
> > 10/4/2008
> > 24797 | English | 52 | 1, | 10006 |
> > 10/4/2008
>
> > Now to the point. I want to make a table where the two are joined
> > like the following via a query though.
>
> > AutoID | StudentID | Grade | First | Last | Subject | Average |
> > CommentID | EndOfWeek
>
> > How can I go about doing this, as a single student could possibly have
> > 10 failing grades in one week? I need to seperate it out this way so
> > I can push it into a word mailing to be mailed to the parents.
>
> > CommentID is not a typpo, it is just the way the dept requested it to
> > be.
>
> > Thanks in advance,
>
> > Jamie
|
|
Did you look at the code?
In the comments it tells you that you need to modify the code by
commenting out certain lines (add an ' at the start of the line)
and uncomment other lines (remove the apostrophe at the beginning of the
line). That assumes you are using DAO in the database.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
jsteenberge wrote:
[Quoted Text] > I am now getting 'Compile error: User-defined type not defined'. I am
> running Access 2007; will this cause the concatenate to not work?
>
> Subjects: Concatenate("SELECT Subject FROM tblSubjects AS G1 WHERE
> G1.AutoIdf = " & S.AutoID) FROM tblGuidance AS S WHERE S.AutoID in
> (SELECT AutoIDf FROM tblSubjects AS G)
>
> That is the line I created to just test the function to see if it will
> work. I don't need to do a WHERE statement containing the average's
> as this is only failing grades in the database. Do you happen to have
> a IM program that we could possibly chat on?
>
> Thanks for your help,
>
> Jamie
>
>
>
> On Dec 30, 5:16 pm, John Spencer <spen...[ at ]chpdm.umbc> wrote:
>> Add both tables to the query
>> Clikc on AutoID in table 1 and drag to AutoIDF in table 2
>> Select the fields you want to display
>> Apply criteria to show only failing grades
>> Run the query.
>>
>> If you want one record per student with failing subjects then you will
>> need to use a concatenate function. Google Duane Hookom + Concatenate
>> to find one you can download. I don't have the URL available.
>>
>> Once you have downloaded the sample and copied the concatenate function
>> into a VBA module in YOUR database, you should be able to use a query
>> that looks something like the following
>>
>> SELECT DISTINCT S.*
>> , Concatenate("SELECT DISTINCT Subject FROM [Table 2] as G1 WHERE
>> G1.Average <70 and G1.AutoIdf = " & S.AutoID) as FailingSubjects
>> FROM [Table 1] as S
>> WHERE S.AutoID in
>> (SELECT AutoIDF
>> FROM [Table 2] as G
>> WHERE G.Average < 70)
>>
>> '====================================================
>> John Spencer
>> Access MVP 2002-2005, 2007-2008
>> The Hilltop Institute
>> University of Maryland Baltimore County
>> '====================================================
>>
>> jsteenberge wrote:
>>> I have two tables that I need to create a mailing letter for. One of
>>> the tables could have over 700 student records and the other table
>>> could have 10,000 records a year depending on student grades.
>>> Table 1
>>> AutoID | StudentID | Last | First | Grade | YOG | SchoolYear
>>> 10001 | 9784 | Doe | Jane | 7 | 2013 | 08
>>> 10002 | 982745 | Doe | John | 6 | 2014 | 08
>>> 10003 | 26454565 | Doe | Jim | 8 | 2012 | 08
>>> 10004 | 234 | Doe | Bob | 7 | 2013 | 08
>>> 10006 | 234234 | Doe | Bill | 7 | 2013 | 08
>>> Table 2
>>> SubjectID | Subject | Average | CommentID | AutoIDf | EndOfWeek
>>> 24789 | Math | 59 | 1,4,2,5 | 10001 |
>>> 10/4/2008
>>> 24790 | Math | 43 | 1,4,2,5 | 10002 |
>>> 10/4/2008
>>> 24791 | Math | 22 | 1,4,2,5 | 10003 |
>>> 10/4/2008
>>> 24792 | PhysEd | 52 | 1,4,2,5 | 10001 |
>>> 10/4/2008
>>> 24793 | History | 61 | 1,4,2,5 | 10001 |
>>> 10/4/2008
>>> 24794 | Science| 32 | 5 | 10002 |
>>> 10/4/2008
>>> 24795 | Math | 49 | 4 | 10003 |
>>> 10/4/2008
>>> 24796 | Art | 58 | 1,5,3,4 | 10006 |
>>> 10/4/2008
>>> 24797 | English | 52 | 1, | 10006 |
>>> 10/4/2008
>>> Now to the point. I want to make a table where the two are joined
>>> like the following via a query though.
>>> AutoID | StudentID | Grade | First | Last | Subject | Average |
>>> CommentID | EndOfWeek
>>> How can I go about doing this, as a single student could possibly have
>>> 10 failing grades in one week? I need to seperate it out this way so
>>> I can push it into a word mailing to be mailed to the parents.
>>> CommentID is not a typpo, it is just the way the dept requested it to
>>> be.
>>> Thanks in advance,
>>> Jamie
>
|
|
I had tried that, but it seems my query was not correct at the same
time, so that was causing a 2nd error.
SELECT DISTINCT S.*, Concatenate("SELECT DISTINCT Subject FROM
tblSubjects AS G1 WHERE G1.Average < 70 AND G1.AutoIDf = " & S.AutoID)
as FailingSubjects FROM tblGuidance AS S WHERE S.AutoID IN (SELECT
AutoIDf FROM tblSubjects AS G WHERE G.Average < 70)
That is what I currently have. From what you sent to me. This tells
me there is an error with the syntax of the subquery.
|
|
Wait! I think I got it. I'll let you know.
Thanks a million!
|
|
|