The "Service" column of the "Service Fee Data Table" is a dollar amount. The
actual "Air Travel Fare" is in the "Air Trave Data Table" it is in the
"Ticket Amnt" column and also is a dollar amount. The division field of both
tables is similar however, for each passenger that shows up in thier division
on the "Air Travel Data" tabel they could show up multiple times on the
"Service Fee Data" table, if the purchase air and hotel services during the
time period. We do not match the service fee to the individual ticket we do
them on a whole by division. so for div #1 the had $15 in serivice fees and
theeir were airline tickets purchsed by div #1 in the sum of $150.20.
The SQL statement for the main report is:
SELECT [Air Travel Data].[AIR TRAVEL ID], [Air Travel Data].DIVISION, [Air
Travel Data].[PASSENGER NAME], [Air Travel Data].[DEPARTMENT NUMBER], [Air
Travel Data].[ISSUE DATE], [Air Travel Data].[TICKET AMT], [Air Travel
Data].[FULL FARE], [Air Travel Data].[LOW FARE], [Air Travel Data].[LOST
SAVINGS], [Air Travel Data].[FARE SAVINGS REASON], [Air Travel Data].DAYSADV,
[Air Travel Data].[TRAVEL PURPOSE]
FROM [Air Travel Data]
WHERE ((([Air Travel Data].[ISSUE DATE])>=[Forms]![View Reports]![Beginning
Issue Date] And ([Air Travel Data].[ISSUE DATE])<=[Forms]![View
Reports]![Ending Issue Date]))
GROUP BY [Air Travel Data].[AIR TRAVEL ID], [Air Travel Data].DIVISION, [Air
Travel Data].[PASSENGER NAME], [Air Travel Data].[DEPARTMENT NUMBER], [Air
Travel Data].[ISSUE DATE], [Air Travel Data].[TICKET AMT], [Air Travel
Data].[FULL FARE], [Air Travel Data].[LOW FARE], [Air Travel Data].[LOST
SAVINGS], [Air Travel Data].[FARE SAVINGS REASON], [Air Travel Data].DAYSADV,
[Air Travel Data].[TRAVEL PURPOSE];
The SQL statement for Subreport1a is:
SELECT [Air Travel Data].[AIR TRAVEL ID], [Air Travel Data].DIVISION, [Air
Travel Data].[PASSENGER NAME], [Air Travel Data].[DEPARTMENT NUMBER], [Air
Travel Data].[ISSUE DATE], [Air Travel Data].[FULL FARE], [Air Travel
Data].[LOW FARE], [Air Travel Data].[TICKET AMT], [Air Travel Data].[LOST
SAVINGS], [Air Travel Data].[FARE SAVINGS REASON], [Air Travel Data].DAYSADV,
[Air Travel Data].[TRAVEL PURPOSE], [Air Travel Data].[AIR TRAVEL ID]
FROM [Air Travel Data]
WHERE ((([Air Travel Data].[ISSUE DATE])>=[Forms]![View Reports]![Beginning
Issue Date] And ([Air Travel Data].[ISSUE DATE])<=[Forms]![View
Reports]![Ending Issue Date]))
GROUP BY [Air Travel Data].[AIR TRAVEL ID], [Air Travel Data].DIVISION, [Air
Travel Data].[PASSENGER NAME], [Air Travel Data].[DEPARTMENT NUMBER], [Air
Travel Data].[ISSUE DATE], [Air Travel Data].[FULL FARE], [Air Travel
Data].[LOW FARE], [Air Travel Data].[TICKET AMT], [Air Travel Data].[LOST
SAVINGS], [Air Travel Data].[FARE SAVINGS REASON], [Air Travel Data].DAYSADV,
[Air Travel Data].[TRAVEL PURPOSE], [Air Travel Data].[AIR TRAVEL ID];
The SQL Statement for Subreport1 is:
SELECT [Service Fee Data].[SERVICE FEE ID], [Service Fee Data].[PASSENGER
NAME], [Service Fee Data].[ISSUE DATE], [Service Fee Data].SERVICE, [Service
Fee Data].[SERVICE FEE], [Service Fee Data].DIVISION, [Service Fee
Data].DEPARTMENT, [Service Fee Data].[EMPLOYEE ID], [Service Fee
Data].[TRAVEL PURPOSE]
FROM [Service Fee Data]
GROUP BY [Service Fee Data].[SERVICE FEE ID], [Service Fee Data].[PASSENGER
NAME], [Service Fee Data].[ISSUE DATE], [Service Fee Data].SERVICE, [Service
Fee Data].[SERVICE FEE], [Service Fee Data].DIVISION, [Service Fee
Data].DEPARTMENT, [Service Fee Data].[EMPLOYEE ID], [Service Fee
Data].[TRAVEL PURPOSE]
HAVING ((([Service Fee Data].[ISSUE DATE])>=[Forms]![View
Reports]![Beginning Issue Date] And ([Service Fee Data].[ISSUE
DATE])<=[Forms]![View Reports]![Ending Issue Date]) AND (([Service Fee
Data].SERVICE)<>"CAR/HOTEL SERVICE FEE"));
:
Hi T-Bird,
Division looks a common field... that could be linked
Which field in Service Fee Data table denotes an Air Travel fare?
Service Provided? If so, what are the values in that field? If not,
how do you match a service fee to a ticket?
What is the SQL for RecordSources for each report/subreport?
send me an email and request my 30-page Word document on Access Basics
(for Programming) -- it doesn't cover VBA, but prepares you for it
because it covers essentials in Access.
Be sure to put at least "Access Basics" in the subject line so that I
see your message...
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
T-bird wrote:
Crystal,
"What fields are in Air Travel Data that you are using?"
Division, Days Advance Purchase, Ticket Amount
"What fields are in Service Fee Data that you are using"
Division, Service Fee, Service Provided
What kind of replationship is between the two tables... one-to-many?
many-to-many? What field(s) is used to link the data?
I have not created any relationship between the two tables and there is
no link created between the two tables. This is due to the fact that the
Service Fee Data table not only contains data related to Air Travel but it
also contains data related to Car Rental and Hotel Fees. So where one client
can be on one table 1 time during a period the could be on the other 3 times.
How do I set the main table up to link both tables?
:
Hi T-Bird,
"Both subreports are in the report footer of the main report"
that explains why it is pulling the same number -- you only have one
current record on one subform in the section ... if you were in a
division group section and just printing everything for each division
seperately, your equations would work -- which is why they do work for
the last division...
Why not set the main report up to link all the data from both tables,
then use the grouping capability with reports and group the information
by division?
What fields are in Air Travel Data that you are using?
What fields are in Service Fee Data that you are using?
What kind of replationship is between the two tables... one-to-many?
many-to-many? What field(s) is used to link the data?
until I know more about the data, I can't help you with the query either
.... but perhaps that is not the best way to go...
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
T-bird wrote:
Both subreports are in the report footer of the main report. There is a table
"Air Travel Data" that feeds the "main report" and "subreport 1a". There is a
table, "Service Fee Data" that feeds subreport 1.
Text boxe controls are set as so:
Total Amt: =Sum([TICKET AMT])
Corp Days Adv: =[Div 21 Day Ticket Price]+[Div14-20 Ticket Price]+[Div 13-
Ticket Price]
Total Service Fees: =Sum([SERVICE FEE])
I hope this answeres your question on the structure. Secondly how do I
create a totals query by division and link to that in the appropriate
recordset
Thanks
:
Hi T-bird,
can you elaborate on the data structures a bit? And also, what sections
of the report your subreports are in and which tables feed the report
and subreports?
Perhaps, instead of using the calculated total from the report, you may
want to use a totals query by division and link to that in the
appropriate recordset
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
T-bird wrote:
I apologize, there are a total of 12 divisions, only division #12 has the
correct %, what is happening is that access is taking the service amount of
divisions 1-11 and dividing them all by the airfare of division 12 rather
than by their own division airfare. So it's going like this #1/#12,
#2/#12.....etc, is hould be #1/#1, #2/#2.....etc (service fee/airfare).
This is what should be happening:
Fees / Airfare = Airfare %
$15.00 $150.20 10.0%
$100.00 $7,718.57 1.3%
$230.00 $18,247.06 1.3%
$90.00 $5,207.70 1.7%
$130.00 $10,291.80 1.3%
$330.00 $20,558.39 1.6%
$50.00 $1,007.80 5.0%
$55.00 $3,382.00 1.6%
$4,155.00 $300,255.24 1.4%
$1,402.00 $24,148.34 5.8%
$10.00 $2,737.10 0.4%
$1,835.00 $96,165.07 1.9%
This is what is happening:
Fees / Airfare = Airfare %
$15.00 $96,165.07 0.0%
$100.00 $96,165.07 0.1%
$230.00 $96,165.07 0.2%
$90.00 $96,165.07 0.1%
$130.00 $96,165.07 0.1%
$330.00 $96,165.07 0.3%
$50.00 $96,165.07 0.1%
$55.00 $96,165.07 0.1%
$4,155.00 $96,165.07 4.3%
$1,402.00 $96,165.07 1.5%
$10.00 $96,165.07 0.0%
$1,835.00 $96,165.07 1.9%
I tried both of the suggestions that you provided me and neither of them
worked. I keep getting the wrong % for divisions 1-11. #12 is the only one
that is right. Is it possible that what I am asking is not possible? could I
create a field within the querey to do the calculation then add it to the
subreport? I've run out of ideas and at a loss for what else to try.
Thanks,
:
Hi T-Bird,
you did not say what was working and what was not... but I see a syntax
error
=[Total Service Fees]/Reports![Executive Travel Summary YTD]![Corp Days Adv]
should be
=[Total Service Fees]/Reports![Executive Travel Summary
YTD].report![Corp Days Adv]
also, in case the subreport doesn't have data...
=IIF( Reports![Executive Travel Summary YTD].report.HasData, [Total
Service Fees]/Reports![Executive Travel Summary YTD].report![Corp Days
Adv], 1)
make sure you are referring to the NAME property of the control and not
the ControlSource...
on this one:
Expression using the Airfare total from Subreport1:
=[Total Service Fees]/Reports![Executive Travel Summary
YTD]!ETLsubrpt1a.Report![Total Amt]
... where is it being used? in subreport2? If so, you can also addrss
subreport1 relatively...
=[Total Service Fees]/parent.ETLsubrpt1a.Report![Total Amt]
If you are interested in some basic info on Access, send me an email and
request my 30-page Word document on Access Basics (for Programming) --
it doesn't cover VBA, but prepares you for it because it covers
essentials in Access.
Be sure to put at least "Access Basics" in the subject line so that I
see your message...
Warm Regards,
Crystal
