Ambitious: Linking tables to click open another table from first table

[sonaljain123]

I want to basically create a table such that if i double click on one of the fields in the table, it opens up another table that constitutes that value? So lets say that particular field contains A which is A=B+C+D+E, so what I want to do is when I click on A, it should open up the table with B,C,D,E that appear as a table itself and that add up to that value?

I am open to use query, macro or any other objects available in MS Access.

Doing this would be a real help.

Thanks!

 

[Guus2005]

So what you want is the Excel-Pivot table doubleclick functionality build in Access?

How would Access know that clicking A is related to the sum of other fields?

There is some table functionality that you can use to emulate the same behaviour. If you relate a table to another table it shows a small + sign. Clicking the + sign expands to the relating records in the other table.

For an example look at the provided sample database Northwind

HTH

 

[sonaljain123]

Hi,

Thanks for your reply. So basically what is happening is that there is approx 50 accounts each of which is made up a number of sub accounts, so in excel we generally do like Sum(SubAccounts Range) to get the value for the main account.

So I want to something similar in access to for each of the account. hence I want to mention A as a sum of B+C+D+E in access so that when I double click A, it shows me the sub accounts.

Thank you!

 

[sonaljain123]

Also since I am new to using this forum, could you please tell me where is Northwind's database located on the website?

Thank you

 

[stopher]

I think it's best to forget everything you ever knew about Excel when embarking on Access.

In Access you might have a table looking like this:

MainAcc____SubAcc____Value
AccA_______Acc123____30
AccA_______Acc124____35
AccA_______Acc123____28
AccB_______Acc223____40
AccA_______Acc224____45


Then you would used a query to sum the value by MainAcc.

Apologies if I'm stating the obvious.

Chris

 

[sonaljain123]

Thanks Chris, I know what you are saying but that is not what I am trying to achieve here. Even if I mention the field as a sum of sub accounts, would I be able to double click the same only to see the constituting sub accounts table separately?

 

[sonaljain123]

it would be great if someone can tell if this is feasible to start with in Access or not using a macro or writing a code or through any other measure...thanks!

 

[scubadiver007]

I think we ought to step back a bit.

You can create a query which can calculate sums by specifying grouping levels using the sigma button.

I get the impression your issue isn't quite so simple. It sounds like a circular argument to me because you need to know what records to select in the first place to get the sum, which you would normally fill out in a form anyway. So I don't see the need for what you are trying to achieve.

Provide more details!

 

[sonaljain123]

Hmm the idea is as follows:

so i have a huge table with data stored of accounts and sub accounts. For each of the account the value is a sum of certain sub accounts. So basically what i want to do is to be able to create a view of the accounts in such a way so that if anyone wants to see the subaccounts in any particular account, the user can double click to open the same.

I hope I have been able to explain properly here. Else I can elaborate further if need be.

Thank you!

 

[scubadiver007]

ok, are each of the sub-accounts exclusive to an account so SA1 can only occur with A1 or can SA1 be associated with A2, A3,... etc?

 

[sonaljain123]

Hmm, so each sub account falls under exactly one big account.

Does that help?

 

[scubadiver007]

That means you have a 1-to-many relationship.

You have one table

Account ID (pk)
Field 1
Field 2

And a table for sub-accounts

Sub-account ID (fk)
Field 1
Field 2

Go to the relationship window, select the two tables and drag the primary key to the foreign key and you have established your relationship.

When you design the form, you select all the fields from both tables. Then you will have a form and a subform. Then you will get a list of all the sub-accounts associated with each account.

Hope that helps. I think you are trying to make your problem more complicated than it has to be.

 

[sonaljain123]

Thanks for that input. I got that idea and I too feel that this might be a better way to do the same. But I have been asked to do it the way I described above.

Hence I was wondering if anyone has ever come across any such idea before, kindly provide some insight. It would be a tremendous help for me.

Thank you!