Complex for me Report

[hthg21]

I know this is an easy one, but I just don't know the way to do it. Here is what I have and am trying to accomplish:


I have a Contacts Table with a list of people, a Flight Table with a list of flights, dates, and passengers (Passengers from the Contacts Table), and a Query that gives me a list of passengers that perform a certain job function (Operator).


I'm trying to create a Report that will show the most recent date that an Operator was on a flight and then depending on how old the date is to have it display in differnt colors.

Any advice or suggestions is very appreciated!

 

[EternalMyrtle]

This is really a querying question.

Make a new query by going to the Create tab and selecting query design. Add your tables, make sure the PK/FK fields are linked properly. Add the necessary fields and set the criteria for the job function field to ="Operator" or whatever the exact value is.

That will show only the passengers who were operators.

Then go to the Design tab and select Totals and for the date field select "last" this will give you only the most recent date the person was an operator.

Now you need a calculated field in your query to calculate the number of days that lapsed. So do something like this in a new column (assuming you are counting number of days):

DaysLapsed: DateDiff("d",[DateFlown],Date())


Now you need to change the colors. I would probably do this with code but you could also use an unbound field in your report with an IIf statement. To use code, in the On Current event of the report, put something like:

If Me.DaysLapsedControlName < x Then
Me.DaysLapsedControlName = RGB(255, 0, 0) 
Else: Me.DaysLapsedControlName =RGB(63, 63, 63)
End If

This assumes you only want to show two colors. You will need to look up the RGB values for the colors. If you want to show more, you can add more If Then statements or ask again.

Good luck!

 

[hthg21]

Thanks for the reply!

 

[hthg21]

I should have explained better as to what I have.

In the Flight table I have a Pass1, Pass1Roll, Pass2, Pass2Roll, Pass3, Pass3Roll, etc., all the way to Pass10.

I have 9 Operators that do a particular roll and I want to keep track of their currency for that roll. They are required to do the roll every 90 days to be current and I want a report showing me all of the Operators and their most recent flight.

 

[EternalMyrtle]

It sounds like your table is not normalized. You shouldn't have pass 1, passroll1, pass 2, passroll 2, etc... When you have a field structure like X1, X2, X3 there is almost always a normalization problem.

Instead you should be using a junction table (one to many) that looks something like this:

tblPassOperators
PassOperatorsID (PK)
Pass
PassRoll
OperatorID (FK to Operators table)

What are passes exactly are they flights? Are they reoccurring or a one time thing unique to each record? If you have a limited set of passes you could also make a Pass table. So something like:

tblPasses
PassID (PK)
PassName

Then, in your junction table you would use PassID instead of listing the pass each time. You could do the same for PassRoll.

Your Operators table would be something like this:

OperatorID
LastName
FirstName
MiddleName
Etc...

In your junction table you would select the OperatorID then select the Pass and Pass Roll and any other info you may need such as PassDate

I don't really know what a pass or pass roll is which makes it hard for me to make firm suggestions.

 

[EternalMyrtle]

Ok, I saw in your message that passes are flights.

I would highly recommend creating a separate table for all of the passes. This way, when you have two or more of the same pass, the data will be uniform. If you don't do this then you risk having messy data (so for example, one pass is listed as EWR-PDX, whereas another is listed as Newark to Portland). You want to avoid any mishaps like this plus the structure I am recommending makes the data more usable.

I am still unsure what a pass roll is...

 

[hthg21]

Thanks for the advice.

The Pass1 = Passenger 1
Pass1Role = Passenger 1 particular Role for that particular flight

I have a table for each flight, one for all the roles, and another for all the passengers, plus other that aren't related to this particular issue.

I'm trying to keep track of each flight, who was on it and what role they performed while on the flight so I can track their currency.

Would it help if I wrote out my Table structure?

Thanks again!

 

[EternalMyrtle]

Ok, now I have a better understanding.

You still need a junction table.

tblPassengersFlights
PassengersFlightsID (PK)
PassID(FK)
FlightID(FK)
PassRoleID(FK)

This assumes you have a table for Passenger Roles. You could also make passenger roles a combo box with a value list rather than making it an FK.

 

[EternalMyrtle]

BTW, make a multiple-fields index on the FlightID and PassID field since a passenger can only be on one flight at a time. This will prevent duplicate records.

If you want feedback on your table structure, perhaps you could write it out and post in the tables forum...

 

[hthg21]

I will make the changes and see where I'm at then.

Thanks again for your advice.

 

[EternalMyrtle]

Ok good luck! Once you restructure the data, I suspect it will be clearer.

Creating the report you want is still not a totally simple thing but it should be much easier with the new structure.