Creating Tables similar to the ones in Northwind database

[sonaljain123]

Hi All,

I am trying to create tables like they are in the northwind database. I already have the individual tables ready which have to be linked to one another like in the database. These table as of now have no relationships defined and are stand alone, though they do have common columns between them.

So to start with that, should I go on to declare the one to many relationships between the tables or should my first step be to write queries and then making the results of the query as a sub datasheet of other table?

In case you have any other suggestions, feel free to let me know about those.

Thank you!

 

[GinaWhipp]

Next step would be to create the Relationships, then you can move on to the queries.

 

[sonaljain123]

Thank you for the input. I also wanted to confirm how to make a one to many relationship in access. So if anyone is aware of the north wind database tables and the relationship between those, you can see it is a one to many relationship. I tried doing that by using the relationshings and cascading as well but it still does not work to be the same as the northwind database.

Any comments on those ones?

 

[GinaWhipp]

Okay...

The ONE side of the Relationship is the Primary Key and the MANY side of the Relationship is the Foreign Key. If you relate to Primary Keys together then you get a ONE-to-ONE which sounds like what you are doing. Hvae a look at...

http://office.microsoft.com/en-us/access-help/guide-to-table-relationships-HA010120534.aspx

 

[sonaljain123]

Hi,

I am having a little trouble in making the tables conceptually similar to the Northwind where in we have sub datasheets.

So my master table is like one huge table with 300 main accounts. Each of this main account is made of 15 sub accounts which add up to this main account value. Each of the sub accounts is made of 10 sub sub accounts which add up to this sub account value. In such a manner, the drill down goes on till level 8.

So basically what I am trying to achieve is to insert sub datasheets in each of the main, sub, sub sub and so on accounts so that the user can see which accounts constitute the main accounts.

This has to be done in the Northwind database tables manner with the + sign functionality.

Does anyone have an idea how to approach this problem?

Thanks a ton!

 

[GinaWhipp]

Please ignore the little plus signs, you are not going to be using those. Please tell me the objective of the database and what industry this applies to so we can talk about your tables.

 

[sonaljain123]

Hi Gina,

The database is to store the values of the balance sheet of a company on a daily basis.

I am not sure what you mean by ignoring the plus signs, since the plus signs basically mean we have a sub datasheet within an account or so.

Does this make it a little more clear for you?

Thanks for helping me out on this one.

 

[GinaWhipp]

Those plus signs cause slowness... The more subdatasheets you add you *may* expereince slowness.

Ummm, perhpas you mean to use Excel? Not that I wouldn't love for you to use Access but tables are not like Excel spreadsheets. And if you are just storing daily balance information what benefit do you think Access will give you over Excel?

 

[sonaljain123]

Hi Gina,

Yes the sub datasheets might cause slowness but that is the layout that I have been asked to re create.

I am sure this can be done in excel but I have been asked to do the same in ms access so I am not sure I have an option here...

Could you think of a way I can lay down the tables?

Thanks!

 

[GinaWhipp]

So sorry as I think Excel would better equipped for this but we move on...

Okay, balance sheet does not tell me much. Do you want to store Client information or any other data? Can you post the tables and fields you have now? Then I can show you PK to FK which will help you see how to relate the tables...

 

[sonaljain123]

Thanks Gina, I really appreciate the help.

Since the exact data is a little confidential, I can send you the dummy table which I am trying to create in ms access.

Would that work?

 

[GinaWhipp]

Oh yes, I don't want the data just the table (or tables) set up...

 

[sonaljain123]

Hi Gina,

I am trying to send that excel sheet over to you, but I am not sure how I can post it here. Should I send the same as a private message or so?

Please guide me on how to send the file over to you.

Thanks!

 

[sonaljain123]

Hi

I have uploaded the file here for your reference. The table consists of some 3,000 rows. The main accounts are 74 in number and then each is made up of a few sub accounts. Then each sub account is made up of sub sub accounts. On the table attached we can differentiate between the account, sub account, sub sub account and so on based on the number of indent levels. So the least number of indents are main accounts, one added indent is sub acccount and so on.

I hope the table makes the issue look clearer now.

Thanks!

 

[GinaWhipp]

Okay, the Excel spreadsheet not helpful. What I need is the Access database. I want to see your table set up.

 

[sonaljain123]

Gina,

I have set up some tables but the way I have them up might not be helpful if I want to insert datasheets. That is why what I have sent is a raw datasheet that I used to set up the initial MS Access tables. I am sure that my current table set up would not be helpful in that case.

Thanks!

 

[GinaWhipp]

Well, belive me when I tell you this spreadsheet is not helping me as it looks like a ledger with no rhyme or reason. I kinda see what you are trying to do but cannot wrap my head around it. I am hoping the tables will help me better see what you are trying to do...

 

[sonaljain123]

Gina, I am attaching the mapping table that I am currently using now. This should give you a much better idea. I am sorry for all the mess but since the data is confidential it is difficult for me.

So basically the table has 8 levels and 8 sub levels. So each smaller level adds up to the value in the bigger level and so on.

For example Level 4 entries of type BALCOM would add up in to form a part of BALLNL in level 3.

Does that make things any better now?

Thank you!

 

[GinaWhipp]

No worries, I don't want the data just the layout. Looking now...

 

[GinaWhipp]

Okay, something like...

tblKarts
kKartID (PK)
kKart

tblGLCccount
glaGLAID (PK)

tblKartsGL
kglID (PK)
kglKartID (FK - Link to tblKarts)
kglGLAID (FK - Link to tblGL)

tblKartRollUp
kruID (PK)
kruKartID (FK - Link to tblKarts)
kruLevelID (FK - Link to tblLevels)
kruSubLevelID (FK - Link to tblSubLevels)
kruCount (Not sure what the number represents)

tblLevels
lLevelID (PK)
lLevel

tblSubLevels
slSubLevelID (PK)
slSubLevel