Need a Little "Table Set" Link Help!

[Rudy16]

I have 4 tables that I use in the set. The Primary is the Year, Month, Month Description table. The fields are:
1) mst_yr c 4
2) mst_mth c 2
3) mst_desc c 10

The 2nd table is a Category table which fields are:
1)int_mth c 2
2) int_catg c 40

The 3rd table is a Sub-category table which fields are:
1) int_2_mth c 2
2) int_catg c 40
3) int_2_catg c 40

My last table is the transaction table which fields are:
1) det_mth c 2
2) int_catg c 40
3) int_2_catg c 40
4) det_desc x 40
5) det_date Date
6) det_paid _to c 40
7) det_paid_amt n 10 2

My set structure now is:
Primary table is: mst_tbl
Parent Key is mst_mth 1 to many linked to
Child table which is: int_tbl
Child Key is int_mth 1 to many

The next link is to my Sub_catg Table:
Parent Table is int_tbl
Parent Key is int_mth 1 to many
Child Table is int_2_tbl
Child Key is int_2_mth 1 to many

The final link is to my Detail Table:
Parent Table is int_2_tbl Detail table is det_tbl
Parent Key is is linked to multiple fields:
Parent Table--Child Table
int_2_mth = det_mth
int_2_catg = int_2_catg
int_catg int_catg

See my attached example:
When I click on 2010 01 and select Income from the Category Browse, I expect to see all Sub-Category for the month of January in the 2nd browse window named Sub_category.

When I click on a Sub_Category entry in the browse Window such as Marion Security I expect to see all entries for Marion Security for the month of January in the 4th Browse window which are the details. If I click on Pensions from the Sub_Category window, I should see all January details for Pensions. This seems to me working.

When I click on Entertainment in the Category Window, I should see Net Flix and Eat Out in the Sub_Category window however I do not. The Sub_Category's for Income and it's Details remain.

Can someone define the correct links for me. I've spent a lot of time and have a mental block on this issue. I had no problem linking 3 tables however when I added the 3th table, this problem occurred.

Thanks,
Rudy

[Keith Hubert]

Re: Need a Little "Table Set" Link Help!

Hi Rudy,

I had no problem linking 3 tables however when I added the 3th table, this problem occurred.

Which would seem to suggest that you have not linked to the correct field.

When you give focus to any row in the second browse, does the third browse filter correctly?

[Clunes]

Re: Need a Little "Table Set" Link Help!

The last link is multiple - so I presume you display three seperate links in your set structure? Based on your description that would be 6 tables in the set (two are alias to the details). I think you will need a serperate browse for each link, Can you send a screen shot of the set structure

[Rudy16]

Re: Need a Little "Table Set" Link Help!

Hi Keith and Clunes,

Decided to attach the app. It's zipped. Create a directory named c:\BudVsAct, dump the file in there and click on it to un-zip. Then run the app.

The tables I tested with are:
mst_tbl
int_tbl
int_2_tbl
det_tbl

The test set is mst_tbl_set_3 and the form is det_form_3. Disregard the others.

This is the 1st stage of the app so the form is not complete nor any reports or other tables to be used as look-ups. I'm just trying to get the links correct first.

Thanks,
Rudy

[Clunes]

Re: Need a Little "Table Set" Link Help!

Rudy,

I tried looking at this but I found your links to be a bit confusing. I also feel that you will have big problems as you go along. The strengths with relationships is to reduce data input so I tried to rationalize your data base. I created a Unique ID for each table and set to auto increment. I then linked each table to these. i believe you are trying to create:
1. Master table based on year
2. category table
3. Supplier or trader table
4. Transaction table

Attached is your db with my mods. Note the extra fields and how the are joined in a new set. View the demo form to see the results

The key principal to the above is yo create on one record for the month, based on the year. You create a category only once no matter what year you are in, so you should look at a drop down selection based on previous values. I did that for you to see that as well.

Let me know if that all makes sense to you

[Keith Hubert]

Re: Need a Little "Table Set" Link Help!

Nice job Mark.

[Rudy16]

Re: Need a Little "Table Set" Link Help!

Thank you so much Mark. Very clean. Mine was confusing and the more I thought about it, the more confused I became. I will study your links to better understand what you have done.

The next step for me will be to add a Budget Amount to each Category and then roll up all sub-category's within the Category and show an Over/Under on the form. For example, When I click on the Income entry for Jan 2010, all transactions for Marion Security, Pensions and Soc Sec will be summed together, shown on the form and compared to the Budget $$ in Income to show a Variance.

Thanks again for the time that you spent to set me straight.

Rudy

[Clunes]

Re: Need a Little "Table Set" Link Help!

No problems. I see it as a bit of payback to all the people on this forum that helped and continue to help me.

I would also look at need for tables and then the type of forms versus reports etc. I think you are trying to achieve something on one view where either a report or query on a form might be better. In your case I might consider getting rid of the master table as it is only date based by adding a date field to the detail table. Base the form adding to the detail records and as you do select a category and sub category to keep it in check. Have the category and subcategories based on lookups where you can add if it one not exist. Then build a few reports - One by date grouped on year, month, category etc

This may help with a much more fluent user input and managment. It really boils down to what you want to achieve by the workflow

[Rudy16]

Re: Need a Little "Table Set" Link Help!

Good morning Mark. I tried entering input to the newly created set and I beleive there are problems with it.

I created a new entry in the Category browse for Jan , 2010 and when I clicked on the Save Record Button, the Sub-Categories from the 1st Entry "Income" were assigned to the newly created Category. When I saved the newly created category, the sub-category window should have been empty allowing me to add new sub-categories to it. That was not the case.

I tried deleting the 3 sub-categories from the sub-category table and it wiped out the Income sub-categories.

[Clunes]

Re: Need a Little "Table Set" Link Help!

I just repeated what you did and did not experience the same problem. Were you on the demo form I made? Also what V10 build are you using? To check which build selet Help - About Alpha 5