Create Table Query

[jsteenberge]

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

 

[John Spencer]

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]

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

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
'====================================================

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

 

[John Spencer]

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
'====================================================

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

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
'====================================================

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

 

[jsteenberge]

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.

 

[jsteenberge]

Wait! I think I got it. I'll let you know.

Thanks a million!

 

[jsteenberge]

ok ... now here is my next dillema

Subjects: Concatenate("SELECT DISTINCT Subject & Chr(9) & Average &
Chr(9) & CommentID FROM tblSubjects AS G1 WHERE G1.AutoIdf = " &
[tblGuidance].[AutoID] & " And G1.EndOfWeekDate = #" & [Forms]!
[frmInelReport]![EndWeekDate] & "#",Chr(13) & Chr(10))

Now getting this data to a word mail merge. I have the mail merge
document all setup with a table in the center to show the subject,
average and commentid. How can I tell word to seperate that column
via comma's? It will always contain three comma's of course as
described in the above sql statement, but could have up to 10 rows per
student. You wouldn't happen to have an answer for this? I'd like
them seperated inside a table, each one in it's own cell.

Thanks,

Jamie

 

[John Spencer]

Sorry, I am stumped at this point.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Pete D.]

Try typing in word directly and record the commands in a macro. See what
you need to send to word to make it do this for you. Might also ask in Word
goup as you have your data now your problem is talking to word. Me if this
was a one time thing, I would change my query to a make table and do the
word merge using that table as data source from within word.

Sorry, I am stumped at this point.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

ok ... now here is my next dillema

Subjects: Concatenate("SELECT DISTINCT Subject & Chr(9) & Average &
Chr(9) & CommentID FROM tblSubjects AS G1 WHERE G1.AutoIdf = " &
[tblGuidance].[AutoID] & " And G1.EndOfWeekDate = #" & [Forms]!
[frmInelReport]![EndWeekDate] & "#",Chr(13) & Chr(10))

Now getting this data to a word mail merge. I have the mail merge
document all setup with a table in the center to show the subject,
average and commentid. How can I tell word to seperate that column
via comma's? It will always contain three comma's of course as
described in the above sql statement, but could have up to 10 rows per
student. You wouldn't happen to have an answer for this? I'd like
them seperated inside a table, each one in it's own cell.

Thanks,

Jamie

 

[John Spencer]

I had some thoughts on this.

Perhaps what you want is the three items sorted by a comma and a space.
ALso if there are no duplicates you might not need the DISTINCT in the
SQL string.

Subjects: Concatenate("SELECT DISTINCT Subject & ', ' & Average &
', ' & CommentID FROM tblSubjects AS G1 WHERE G1.AutoIdf = " &
[tblGuidance].[AutoID] & " And G1.EndOfWeekDate = #" & [Forms]!
[frmInelReport]![EndWeekDate] & "#",Chr(13) & Chr(10))

But that just gives you one string. You did try a tab character and I
would think that would work. I think you might have to build a macro in
word to handle this.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Sorry, I am stumped at this point.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

ok ... now here is my next dillema

Subjects: Concatenate("SELECT DISTINCT Subject & Chr(9) & Average &
Chr(9) & CommentID FROM tblSubjects AS G1 WHERE G1.AutoIdf = " &
[tblGuidance].[AutoID] & " And G1.EndOfWeekDate = #" & [Forms]!
[frmInelReport]![EndWeekDate] & "#",Chr(13) & Chr(10))

Now getting this data to a word mail merge. I have the mail merge
document all setup with a table in the center to show the subject,
average and commentid. How can I tell word to seperate that column
via comma's? It will always contain three comma's of course as
described in the above sql statement, but could have up to 10 rows per
student. You wouldn't happen to have an answer for this? I'd like
them seperated inside a table, each one in it's own cell.

Thanks,

Jamie

 

[jsteenberge]

What if I created this as a report in access?

How would I do a page break between each row, and then the tab
seperated values how would I make that show up in a table?

If I could keep it all in a database that would be ideal. I am not a
report wizard. :-(