Cross Tab advice - data from 2 tables

[jcfowl]

I have to create a report where there is information to be summarized from 1 of two tables:

TblMaster_Schedule (1 to 1)-> TblMaster_Day
TblMaster_Schedule (0 to n)-> TblSpot_Changes

For the crosstab:
The rows run off of The Location field in the TblMaster_schedule table and the columns run off The Sequence field in the TblMaster_Day table. If a record corresponding to the Location and Sequence in those tables exists in the TblSpot_Changes table then I want to show that, otherwise I want to show the info from the TblMaster_Day table. I am having problems especially due to the 0,n relationship on the TblSpot_changes table.

Any advice would help, a crosstab is not a requirement but it is the way I thought everything would work the best.

 

[lbass]

First, create an equal join from TblMaster_Schedule ->TblMaster_Day and a left join from TblMaster_Schedule -> TblSpot_Changes. Use a crosstab, with TblMaster_Schedule.Location for the row, and TblMaster_Day.Sequence as the column. Create a formula something like this to use for the field to summarize:

if not isnull({TblSpot_Change.field}) then{TblSpot_Changes.field} else
{TblMaster_Day.field}//substitute your fields and then determine the appropriate summary for the crosstab.

This might get you close to what you're looking for.

-LB

 

[jcfowl]

lbass, Thanks for the response. What you posted is the exact setup I have right now but there is a further complication. The data summarized describes the Item Type being delivered to a customer. I need to output the Highest ranking item, which is ranked based on it's type. I made a Formula Field that converts the Type column of the table to a numeric Rank but I am not allowed to place this in the CrossTab for some reason(it doesn't show up as an option). How can I go about summarizing this Text field with a crosstab summary? Any ideas are much appreciated.

 

[lbass]

What is the formula you used for the ranking? What is the exact layout of the crosstab--this is the first you've mentioned a "type" column and I wonder how that fits with the sequence and location fields that you mentioned earlier. A visual representation of how you would like the crosstab to look would help.

-LB

 

[jcfowl]

I appreciate all the responses but I got it to work.

The Tables:
TblMaster_Schedule -> TblMaster_Day
TblMaster_Schedule -> TblSpotChanges

The TblSpotChanges is a duplicate of the TblMaster_Day table. Both contain the fields fkMaster_Schedule linking a daily schedule to a master loction. SpotChanges are alterations to a regular schedule. If there is a spot change then I want to show that value, otherwise show the MasterDay value.

To fix the crosstab I changed the linking expert:
TblMaster_Schedule -> TblMaster_Day
TblMaster_Day -> TblSpotChanges (instead of linking to the master schedule)

The ranking of items to show in the crosstab was easier than I expected. I used a SELECT CASE statement in a formula to assign a numeric value based on the Type field in the MasterDay and SpotChanges table(Feed=1, BOX=2...) I sorted the records by that and created a formula field in the header of my group that contains the Type for the first record in the group based on the Custom Numeric Sorting. This is what I placed in the crosstab and the results were exactly what I wanted. The key was linking the TblSpotChanges table to the TblMasterDay table instead of the TblMAster_Schedule.