OK how do I make 4 tables show

[mentairia]

Need someone to take following file and show me how to seperate it into 4 tables all on same page. I want it seperated by 10 days 30 days 60 days and 90 days.

Thanks for the help

Tony D.

Careful_eval.zip

[-Queue-]

I am not sure if this is what you are asking, but sort (or create a script to sort) the records by Ten Before [descending], Thirty Before [descending], Sixty Before [descending], and Nintey Before [descending], in that order.

[mentairia]

Did you look at my sample?

I need each of the tables to only show the records that correspond with there sort.

[Wim Decorte]

Curveball: are you sure you want to split your data out like this? Seems to me you'd end up with 4 tables with the same data, except for the date range.

usually that's a big no-no from a data normalization point of view. If you need to make changes to the data structure of the one "logical data unit" you'll need to make that one change in 4 tables.

What's the underlying reason you want to split it out? Reporting? Did you try it in 1 table and did it not work for some reason? If so: what did not work, maybe we can help figure that out and keep everything in one table.

[mentairia]

The 10 30 60 90 will be how a sales agent knows of upcoming contract ends to go resale it before that date.

[Ender]

You should try this with two tables and four relationships instead. Suppose you have an Agent table and a Contract table, you would then have a relationship for each date range (use one TO of Agent and four TOs of Contract.) This allows you to have a portal of each range on a layout in Agent, so you see the Contracts for each date range grouped together.

This would require several unstored calcs in Agent to act as the parent keys for each date range:

AgentID (number)

DatePlus10 (calculation, date result) = get(currentdate)+10

DateMinus10 (calculation, date result) = get(currentdate)-10

DateMinus30 (calculation, date result) = get(currentdate)-30

DateMinus60 (calculation, date result) = get(currentdate)-60

DateMinus90 (calculation, date result) = get(currentdate)-90

Also assume each Contract has an AgentID and a Contract Date. Then for the relationships, add four table occurences of Contract (one for each range,) and drag a relationship between Agent::AgentID and Contract::AgentID. Then double click the relationship line between the table occurences to bring up the Edit Relationship dialog. In there, you can add additional criteria for the relationships, including the date ranges. The relationships would look like:

Agent <=> Contract Within 10 Days =

Agent::AgentID = Contract Within 10 Days::AgentID

AND Agent::DateMinus10 <= Contract Within 10 Days::Contract Date

AND Agent::DatePlus10 >= Contract Within 10 Days::Contract Date

Agent <=> Contract 10to30 Days =

Agent::AgentID = Contract 10to30 Days::AgentID

AND Agent::DateMinus30 <= Contract 10to30 Days::Contract Date

AND Agent::DateMinus10 >= Contract 10to30 Days::Contract Date

Agent <=> Contract 30to60 Days =

Agent::AgentID = Contract 30to60 Days::AgentID

AND Agent::DateMinus60 <= Contract 30to60 Days::Contract Date

AND Agent::DateMinus30 >= Contract 30to60 Days::Contract Date

Agent <=> Contract 60to90 Days =

Agent::AgentID = Contract 60to90 Days::AgentID

AND Agent::DateMinus90 <= Contract 60to90 Days::Contract Date

AND Agent::DateMinus60 >= Contract 60to90 Days::Contract Date

With these relationships in place, you can then use a Form View layout based on Agent, and insert four portals of Contract using each of those relationships. Insert whichever related fields you wish onto each of the portals to show the related Contract info for each range.

[mentairia]

Can you show me a sample even with no records just so I can see your idea more clearly.

THanks

Tony D.