Multiple queries in report

[Perplexed]

Using Access 2003. I have 3 tables: Table 1 (Passengers) = name, address,
etc.; Table 2 (Appointments) = date, time, cancelled, reason, etc.; Table 3
(Suspensions) = FirstWarning, Letter Sent, Suspension, etc. All tables are
joined by the primary key "CustomerID" and table 1 and 2 are one to many and
table 1 and 3 are one to one. I have 3 queries one each table pulling the
fields that I want on my report. The main report uses the table1query so I
get all passengers in the database. I used two subreports using the queries
from the other two tables. Reason: so I could print out a list of cancelled
appts per passenger based on a certain criteria and then only show the fields
in the third table once, not for each appointment record. The report looks
great and provides the data exactly how I would like it to be. Problem is
that it prints out for every passenger in table 1 making the report about 60
pages. I'd like to filter on only passengers with cancelled appts w/ reasons
greater than 1. When I do this I get multiple sections for each passenger
based on the number of cancelled appts. I've read some of the replys;
however, the coding that is suggested doesn't give me any clues as I don't
know where to put it. Hoping that someone out there understands what I'm
tring to do and can explain it simply.

 

[MGFoster]

Using Access 2003. I have 3 tables: Table 1 (Passengers) = name, address,
etc.; Table 2 (Appointments) = date, time, cancelled, reason, etc.; Table 3
(Suspensions) = FirstWarning, Letter Sent, Suspension, etc. All tables are
joined by the primary key "CustomerID" and table 1 and 2 are one to many and
table 1 and 3 are one to one. I have 3 queries one each table pulling the
fields that I want on my report. The main report uses the table1query so I
get all passengers in the database. I used two subreports using the queries
from the other two tables. Reason: so I could print out a list of cancelled
appts per passenger based on a certain criteria and then only show the fields
in the third table once, not for each appointment record. The report looks
great and provides the data exactly how I would like it to be. Problem is
that it prints out for every passenger in table 1 making the report about 60
pages. I'd like to filter on only passengers with cancelled appts w/ reasons
greater than 1. When I do this I get multiple sections for each passenger
based on the number of cancelled appts. I've read some of the replys;
however, the coding that is suggested doesn't give me any clues as I don't
know where to put it. Hoping that someone out there understands what I'm
tring to do and can explain it simply.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your "main report" query is, I'm guessing, using just the table
Customers, correct? If so, to filter canceled appts w/ reasons > 1
you'll need to INNER JOIN the Appointments table to the Customers table
so the "canceled" and "reason" columns can be used in the query's
criteria. You don't have to use the data from Appointments in the main
query, you just have to use it to filter the customers to those that
have canceled. So, something like this:

SELECT ... <Customers column list> ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason > 1
.... rest of query, if there is any GROUPing ...

I don't know what you have in the column A.reason, since you said
"greater than 1" I'm assuming there is a number there. If there are
words then you'll have to figure out another comparison expression to
find whatever you mean by "greater than 1."

You could also keep the INNER JOIN to the Appointments table and refrain
from putting the Appointments columns in the WHERE clause and use the
DoCmd.OpenReport's WhereCondition parameter. E.g.:

DoCmd.OpenReport "Report Name", _
WhereCondition:="cancelled = True And reason > 1"

The WhereCondition parameter is just the WHERE clause w/o the word
"WHERE." This way you can use the same query to get all customers or
just those that have canceled, or any other criteria that involves the
Appointments table, just by adding the WhereCondition, or leaving it
off.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSeZ+/oechKqOuFEgEQLIggCgtPotoEGL87NfivTXXEZ+Z46O+Y0AoK81
QsKjZIKMRHcp30SjPM+EW72Y
=8Wn8
-----END PGP SIGNATURE-----

 

[Perplexed]

Thanks MG. I think I understand the concept you are explaining; however I
don't know where to input this code. I've tried it in the query design, I've
tried it in the Relationships but don't seem to come close. Sorry for my
inexperience in this area.

Also, in the attempts to do this in the wrong places, I have somehow
corrupted my main table and now when I try to add a new client, I keep
getting an error msg that the entry will cause duplicate records. I deleted
all the relationships and was successful in adding one new client. I
replaced the relationships and started adding appointments to existing
clients but when I tried to add another new client, the error msg came up
again. I've discovered that when I tried to add a new client in the main
table that the CustomerID (PrimaryKey) which is indexed with "No Duplicates"
is trying to add a number that already exists. HELP ME! I'm getting really,
really frustrated with Access.
And, again THANKS! for all your help and patience.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sorry, I thought you'd be familiar with Visual Basic for Applications
(VBA). The code goes in a VBA module, either a stand-alone module or a
form's module. In Form Design view, on the menu bar, click View > Code.

I'd probably put the code in a form that would take the criteria for the
report. The form would have a CheckBox that indicates if the report
should be only Canceled appointments - named "chkCanceled." There would
be a CommandButton (which I named "cmdOK") on the form and the OnClick
event procedure of that button would run the DoCmd.OpenReport.
Something like this (select [Event Procedure] from the drop-down menu in
the CommandButton's OnClick property; then click the Build button to the
right of the field):

Private Sub cmdOK_Click()

If Me!chkCanceled = True Then

' print just canceled appointments
DoCmd.OpenReport "Report Name", WhereCondition:= _
"cancelled = True And reason > 1"
Else

' print every one
DoCmd.OpenReport "Report Name"
End If

End Sub

NOTE: There is an underscore character at the end of the OpenReport
line. This is a "line continuation" token in VBA. It must be there so
VBA will read the next line, which is a parameter of the OpenReport
method.

Table Problem (before doing this MAKE A BACKUP OF THE DATABASE!!!):

It sounds like your CustomerID an AutoNumber, correct? If so, I believ
it is screwed up. You'll have to create a copy of your Customers table
(named newCustomers) without any data, then copy your old data into the
new table.

How to make data-free table copy:

DO NOT try to enter any more data in old Customers table.

A. Highlight table in Database Window.
B. Copy it [Ctrl-C] and Paste it [Ctrl-V].
C. A dialog box will appear asking for the new name of the table -
enter the name. It will also have an Option Button for "Structure Only"
- click that one and Click the OK button.

How to get the old data into the new table:

1. rename the old Customers table to "oldCustomers."
2. copy the current contents of the oldCustomers table into the new
Customers table - like this (in a query's SQL View):

INSERT INTO newCustomers SELECT * FROM oldCustomers

then run the query.

3. recreate the Relationships between the newCustomers table and the
appropriate tables (use the oldCustomers table's Relationships as a
guide).

Try to enter new data in the newCustomers table (more than one record).
If it works delete the oldCustomers table.

4. rename the newCustomers table to Customers.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSegW5YechKqOuFEgEQKCtQCg2zRew/LEC7PIxe1QcxkV3qMG15cAnjcH
+SFYG4VkrwqOdyDuh2vNAoxp
=M3Ph
-----END PGP SIGNATURE-----

 

[Perplexed]

MG,
Thanks for all the help.
1) The instructions for fixing the main table malfunction with the
customerID worked great!
2) The instructions for putting the code into the query sql view worked
great too. The query returned only clients with cancelled appts >1. The
only problem is that when I tried to make a report based on this query just
selecting the name, address, phone fields, I still get multiple lines per
name based on the number of cancelled appt criteria; i.e., if the client has
5 cancelled appts greater than 1, there is 5 occurrences of the client name,
address & phone.
I gave in and set the properties of these fields to 'Hide Duplicates'.
However, when I drop in the subform based on the query for appts, the data
doesn't line up and it then repeats again per client line, i.e., for every
hidden line the list of appts show so now the same i.e., has 25 lines of
appts.

Should I just forget this and accept the fact that it won't work or is there
a solution?

Again, thanks for your quidance and patience -- I am learning a lot from
your tutoring.
=========================

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sorry, I thought you'd be familiar with Visual Basic for Applications
(VBA). The code goes in a VBA module, either a stand-alone module or a
form's module. In Form Design view, on the menu bar, click View > Code.

I'd probably put the code in a form that would take the criteria for the
report. The form would have a CheckBox that indicates if the report
should be only Canceled appointments - named "chkCanceled." There would
be a CommandButton (which I named "cmdOK") on the form and the OnClick
event procedure of that button would run the DoCmd.OpenReport.
Something like this (select [Event Procedure] from the drop-down menu in
the CommandButton's OnClick property; then click the Build button to the
right of the field):

Private Sub cmdOK_Click()

If Me!chkCanceled = True Then

' print just canceled appointments
DoCmd.OpenReport "Report Name", WhereCondition:= _
"cancelled = True And reason > 1"
Else

' print every one
DoCmd.OpenReport "Report Name"
End If

End Sub

NOTE: There is an underscore character at the end of the OpenReport
line. This is a "line continuation" token in VBA. It must be there so
VBA will read the next line, which is a parameter of the OpenReport
method.

Table Problem (before doing this MAKE A BACKUP OF THE DATABASE!!!):

It sounds like your CustomerID an AutoNumber, correct? If so, I believ
it is screwed up. You'll have to create a copy of your Customers table
(named newCustomers) without any data, then copy your old data into the
new table.

How to make data-free table copy:

DO NOT try to enter any more data in old Customers table.

A. Highlight table in Database Window.
B. Copy it [Ctrl-C] and Paste it [Ctrl-V].
C. A dialog box will appear asking for the new name of the table -
enter the name. It will also have an Option Button for "Structure Only"
- click that one and Click the OK button.

How to get the old data into the new table:

1. rename the old Customers table to "oldCustomers."
2. copy the current contents of the oldCustomers table into the new
Customers table - like this (in a query's SQL View):

INSERT INTO newCustomers SELECT * FROM oldCustomers

then run the query.

3. recreate the Relationships between the newCustomers table and the
appropriate tables (use the oldCustomers table's Relationships as a
guide).

Try to enter new data in the newCustomers table (more than one record).
If it works delete the oldCustomers table.

4. rename the newCustomers table to Customers.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSegW5YechKqOuFEgEQKCtQCg2zRew/LEC7PIxe1QcxkV3qMG15cAnjcH
+SFYG4VkrwqOdyDuh2vNAoxp
=M3Ph
-----END PGP SIGNATURE-----

Thanks MG. I think I understand the concept you are explaining; however I
don't know where to input this code. I've tried it in the query design, I've
tried it in the Relationships but don't seem to come close. Sorry for my
inexperience in this area.

Also, in the attempts to do this in the wrong places, I have somehow
corrupted my main table and now when I try to add a new client, I keep
getting an error msg that the entry will cause duplicate records. I deleted
all the relationships and was successful in adding one new client. I
replaced the relationships and started adding appointments to existing
clients but when I tried to add another new client, the error msg came up
again. I've discovered that when I tried to add a new client in the main
table that the CustomerID (PrimaryKey) which is indexed with "No Duplicates"
is trying to add a number that already exists. HELP ME! I'm getting really,
really frustrated with Access.
And, again THANKS! for all your help and patience.