Sub Report Question

[pungigi]

I have a detail report of 40 fields, I need to create 1 summary report summarizing several areas of the detail, some areas will be summarized in different ways, is there an easy way to do this? I am not real familiar with sub reports so am not sure if that would work. I currently have 15 queries created that cover the summary information I am just unsure how to combine in one area. Any help would be GREATLY appreciated.

TIA

 

[dhookom]

Subreports could work well. Using expressions in group footers may work well.

Could you provide any additional information?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[pungigi]

Ok, I have a table of 40 fields, I am creating a summary based on a few of these fields as follows:

In my table there is a "Trip Status" field, for the first part of my summary I need...

Completed 1 Way Trips ( this is any trip with a "Trip Status" of S1 or SQ and a "Trip Type" of T, F, or P, counts as 1, if the "Trip Type" is R then it should count as 2 )

Denied Trips (Any trip with a status of D counts as 1)
Waiting Trips (Any trip with a status of W counts as 1)
Member Cancelled Trips (Any trip with a status of X5 counts as 1)
Vendor Cancelled Trips (Any trip with a status of XV counts as 1)
Other Cancelled Trips (Any trip with a status of X* counts as 1)
Member NS (Any trip with a status of X3 counts as 1)
Vendor NS (Any trip with a status of VN counts as 1)

I have created seperate queries for each category, 8 queries and I need to combine them somehow onto one report. Above queries each have a criteria set specifying begin date, end date and plan id.

For the second part I am summarizing which needs to go on the same report as above information, I have a crosstab query with the row being vendor name and the columns being vehicle type, I need a count as follows:

C P A G Total
Vdr 1 1 0 0 4 5
Vdr 2 3 2 0 5 10
Vdr 3 7 1 0 3 11
Vdr 4 4 0 0 2 6

Total 15 3 0 14 31

I have a parameter tied to this specifying begin date, end date and plan id, I also NEED to be able to specify the TRIP Status and haven't yet figured out how to do this because their are 7 different statuses I need to include.

Any help would be appreciated.

 

[dhookom]

You mentioned "summary based on a few of these fields" but I only find mention of one field "Trip Status". There are references to possible other field that might contain category, a date, trip type, plan id, vendor, and vehicle type but these are guesses.

Could you provide some sample "raw" data of significant fields with table and field names? Then provide what you expect for a result in your final query.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[pungigi]

Is there a way to email you a spreadsheet???

 

[pungigi]

If you copy out to an excel type program it will be easier to understand.




ID Appt Date Appt Time Call Date Call Time First Name Last Name MMEDC # Healthcare ID Age Trip # Vendor Name Minority Women PU Name PU Address PU City PU State PU Zip PU County Member Phone PU RMU Dest Name Dest Address Dest City Dest State Dest Zip Dest County Dest RMU Vehicle Type Trip Status Wheelchair C/W/C Pregnant Car Seats Trip Type Addl Pass Pass w/Appt Trip Reason Trip Cost Miles Plan Id
6 20040901 10 20040831 2306 First Last 12345678 0 12345678 Vendor Name N N Pick Up 5824 TERRY AVE APT A Ste Genevieve MO 63120 SAINT LOUIS CITY 3143853621 M STL CHILDRENS HOSPITAL 4546000 1 CHILDRENS PL SAINT LOUIS MO 63110 SAINT LOUIS CITY M C S1 N N N 1 R 1 0 12 8 4 0011
7 20040901 200 20040901 19 First Last 12345678 0 12345678 Vendor Name N N Pick Up 11272 CEDAR POINTE DR N Ste Genevieve MO 55305 HENNEPIN 9525446438 M CHILDRENS HOSPITALS & 8136100 2525 CHICAGO AVE MINNEAPOLIS MN 55404 HENNEPIN M C S1 N N N 1 R 1 0 12 16 9 0158
8 20040901 210 20040901 1307 First Last 12345678 9 12345678 Vendor Name N N Pick Up 400 JOHNSON ST Ste Genevieve MO 49707 ALPENA 9897470788 U RITE AID PHARMACY 989-739-4255 5280 N HURON RD OSCODA MI 48750 IOSCO U C S1 N N N 0 T 1 0 21 63 49 0028
9 20040901 331 20040825 1049 First Last 12345678 59 12345678 Vendor Name Y N Pick Up 590 BIRCH RD Ste Genevieve MO 65672 TANEY 4173345097 U FAMILY PHARMACY 180 MALL RD HOLLISTER MO 65672 TANEY U C S1 N N N 0 T 0 0 21 10 0 0050
10 20040901 430 20040816 844 First Last 12345678 19 12345678 Vendor Name Pick Up 86 WILSON PKWY Ste Genevieve MO 23707 PORTSMOUTH CITY 7572359046 M HARBOR VIEW 757/675/5800 5818 HARBOUR VIEW BLVD SUFFOLK VA 23435 SUFFOLK CITY M C W1 N N N 0 T 0 0 12 0 0123
11 20040901 430 20040826 1011 First Last 12345678 88 12345678 Vendor Name N N Pick Up 8003 MABELVALE CUT OFF RD Ste Genevieve MO 72103 SALINE 5014554897 M VERA MASSIE 18100 FAWN TREE DR LITTLE ROCK AR 72210 PULASKI M C XV N Y N 0 F 0 0 80 11 8 0147
12 20040901 445 20040820 1737 First Last 12345678 50 12345678 Vendor Name N N Pick Up 1118 ISADORE ST Ste Genevieve MO 64501 BUCHANAN 8162320176 M GAMBRO HEALTHCARE 816-671-1948 5514 CORPORATE DR STE 100 SAINT JOSEPH MO 64507 BUCHANAN M P SQ Y N N 0 R 0 0 04 25 7 0050
13 20040901 500 20040820 1735 First Last 12345678 55 12345678 Vendor Name N N Pick Up 1958 ATCHISON AVE APT 195 Ste Genevieve MO 65340 SALINE 6608310869 U RENAL CARE GROUP 660 886 9080 359 W MORGAN ST MARSHALL MO 65340 SALINE U P SQ Y N N 0 T 0 0 04 25 0050
14 20040901 500 20040820 1738 First Last 12345678 38 12345678 Vendor Name N N Pick Up 530 E PARKER ST Ste Genevieve MO 65349 SALINE 6605292050 U RENAL CARE GROUP 660-886-9080 359 W MORGAN ST MARSHALL MO 65340 SALINE U C XX N N N 0 R 1 0 04 45 15 0050
15 20040901 500 20040820 1738 First Last 12345678 63 12345678 Vendor Name N N Pick Up 815 S 21ST ST Ste Genevieve MO 64507 BUCHANAN 8162328232 M GAMBRO HEALTHCARE 816-671-1948 5514 CORPORATE DR SAINT JOSEPH MO 64507 BUCHANAN M C SQ N N N 0 R 0 0 04 15 4 0050
16 20040901 500 20040820 1739 First Last 12345678 30 12345678 Vendor Name Y N Pick Up 3342 GOLDEN OAKS LN Ste Genevieve MO 23321 CHESAPEAKE CITY 7574833342 M CHESAPEAKE DIALYSIS 757 523 0666 1400 CROSSWAYS BLVD CHESAPEAKE VA 23320 CHESAPEAKE CITY M C SQ N N N 0 R 0 0 04 35 8 0123
17 20040901 500 20040820 1740 First Last 12345678 42 12345678 Vendor Name N N Pick Up 16834 SAN JUAN DR Ste Genevieve MO 48221 WAYNE 3133400812 M DETROIT NW DIALYSIS 653-2850 7800 W OUTER DR DETROIT MI 48235 WAYNE M C DA N N N 0 R 0 0 04 12 0 0040
18 20040901 500 20040820 1740 First Last 12345678 62 12345678 Vendor Name Y N Pick Up 32546 JAMES CURTIN DR Ste Genevieve MO 48048 MACOMB 5867494463 M DAVITA DIALYSIS 5588160 (586) 11885 E 12 MILE RD WARREN MI 48093 MACOMB M P SQ Y Y N 0 R 0 0 04 50 0 0024
19 20040901 500 20040820 1740 First Last 12345678 33 12345678 Vendor Name N N Pick Up 1214 GRISWOLD ST APT 903 Ste Genevieve MO 48226 WAYNE 3139638161 M DIALYSIS CNTR 313-291-7800 24565 HAIG ST TAYLOR MI 48180 WAYNE M C SQ N Y N 0 R 0 0 04 54 0 0040
20 20040901 500 20040820 1741 First Last 12345678 52 12345678 Vendor Name N N Pick Up 9455 GRANBY ST APT 3 Ste Genevieve MO 23503 NORFOLK CITY 7572003020 M CHESAPEAKE RTC (757) 523-2311 1400 CROSSWAYS BLVD CHESAPEAKE VA 23320 CHESAPEAKE CITY M P X5 Y N N 0 R 0 0 04 33 10 0122


Expectation CRITERIA Begin Date & End Date This is based on Column B
Plan ID This is based on Column AP
Completed 1 Way Trips 17 This is based on Column AE being a S1 or SQ AND Column AJ if a T, F or P counts 1 if R counts 2

Denied Trips 1 This is based on Column AE being a D*
Waiting Trips 1 This is based on Column AE being a W*
Member Cancelled 1 This is based on Column AE being a X5
Vendor Cancelled 1 This is based on Column AE being a XV
Other Cancelled 1 This is based on Column AE being a XX
Member No Shows 0 This is based on Column AE being a X3
Vendor No Shows 0 This is based on Column AE being a VN

PARAMETER Begin Date & End Date This is based on Column B
Plan ID This is based on Column AP
C P Total
Vendor Name 1 1 This is based on Column AD AND Column AE being a S1 or SQ
Vendor Name 1 1 This is based on Column AD AND Column AE being a S1 or SQ
Vendor Name 1 1 This is based on Column AD AND Column AE being a S1 or SQ
Vendor Name 1 1 This is based on Column AD AND Column AE being a S1 or SQ
Vendor Name 1 1 This is based on Column AD AND Column AE being a S1 or SQ
Vendor Name 1 1 This is based on Column AD AND Column AE being a S1 or SQ
Vendor Name 1 1 This is based on Column AD AND Column AE being a S1 or SQ
Vendor Name 1 1 This is based on Column AD AND Column AE being a S1 or SQ
Vendor Name 1 1 This is based on Column AD AND Column AE being a S1 or SQ
Vendor Name 1 1 This is based on Column AD AND Column AE being a S1 or SQ

 

[pungigi]

Is there anyone that can help with this problem??

 

[dhookom]

Do you see how difficult this is to understand? Can't you limit this to only the columns/fields that make a difference in your question? I tried pasting into Excel and it doesn't look much better.

We certainly don't need to see all 40 fields. Please try again with only significant fields. Also please close out your other thread if it is the same as this and just point them here.

BTW: I looked hard at the information since it mentions the hospital that saved my son's life in October of 1993.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[pungigi]

Ok, SORRY, here I go again.

BTW, VERY GLAD about your son.

Here it is.

ApptDate VehType Trip TripType PlanId PZip DZip VdrName
20040901 C S1 R 0011 63376 63301 VN
20040901 C S1 R 0158 63301 63376 VN
20040901 C S1 T 0028 63114 63114 VN
20040901 C S1 T 0050 63376 63376 VN
20040901 C W1 T 0123 63103 63104 VN
20040901 C XV F 0147 63104 63101 VN
20040901 P SQ R 0050 63131 63128 VN
20040901 P SQ T 0050 63376 63376 VN
20040901 C XX R 0050 63304 63308 VN
20040901 C SQ R 0050 63304 63108 VN
20040901 C SQ R 0123 63376 63123 VN
20040901 C DA R 0040 63301 63124 VN
20040901 P SQ R 0024 63101 63102 VN
20040901 C SQ R 0040 63102 63101 VN
20040901 P X5 R 0122 63103 63103 VN

Expectation
Simple Query
CRITERIA
Begin Date & End Date Based on Column A
Plan ID Based on Column E

*Based on Column C being a S1 or SQ & Column D if a T, F or P counts as 1, if R counts as 2

Completed 1 Way Trips 17 See above *Statement
Denied Trips 1 Based on Col C being a D*
Waiting Trips 1 Based on Col C being a W*
Member Cancelled 1 Based on Col C being a X5
Vendor Cancelled 1 Based on Col C being a XV
Other Cancelled 1 Based on Col C being a XX
Member No Shows 0 Based on Col C being a X3
Vendor No Shows 0 Based on Col C being a VN

Each of the above 8 categories is a seperate query
___________________________________________________________
Expectation
Crosstab Query
PARAMETER
Begin Date & End Date This is based on Column A
Plan ID This is based on Column E

C P Total
Vendor Name 1 1
Vendor Name 1 1
Vendor Name 1 1
Vendor Name 1 1
Vendor Name 1 1
Vendor Name 1 1
Vendor Name 1 1
Vendor Name 1 1
Vendor Name 1 1
Vendor Name 1 1

Total 7 3 10

Above is based on Column B & H AND Column C being a S1 or SQ
__________________________________________________________

Expectation
Crosstab Query
PARAMETER
Begin Date & End Date This is based on Column A Plan ID This is based on Column E
Dest Zip Dest Zip Total
PU Zip 1 1 This is based on
PU Zip 1 1 This is based on
PU Zip 1 1 This is based on
PU Zip 1 1 This is based on
PU Zip 1 1 This is based on
PU Zip 1 1 This is based on
PU Zip 1 1

Total 5 2 7
Above is based on Column F & G AND Column C being a S1 or SQ

 

[dhookom]

To get you started, create a function that can be used in queries that returns the number of trips

Public Function CountTrips(pstrTrip as String, _
	 pstrTripType as String) as Integer
    Select Case pstrTrip
        Case "S1","SQ"
            If pstrTripType = "R" Then
                CountTrips=2
              Else
                CountTrips=1
            End If
        'you may need to add more values
        Case "DA", "W1" ,"X5","XV","XX"
            CountTrips=1
        Case "X3","VN"
            CountTrips=0
        Case Else
            CountTrips =1
   End Select
End

You can then create a Crosstab with a value column like:
theVal:CountTrips([Trip],[TripType])

Search google or this group on how to use form controls as parameters in crosstab queries.

Come back with specific questions.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]