Creating Tables similar to the ones in Northwind database

[sonaljain123]

Gina,

I am not entirely sure I follow the post. So it seems you have given me the names of the columns of the 6 tables I should try to lay down. But I am not sure which column name refers to the columns names in the dummy table I sent across...

would it be possible to be a little more clear on the same?

Thanks a ton!!

 

[sonaljain123]

Hi,

I have attached the exact tables that I have in my database for now and some of the tables looks very similar to what you have given me.

But it is different from the way you have laid them down.

Thanks!!!

 

[GinaWhipp]

Hmmm, I see no attachment...

 

[sonaljain123]

Sorry about that, I am zipping the file and sending it across now.

Thank you for your help.

 

[GinaWhipp]

I did not abandon you... at a Client right now, I will look at the database later tonight.

 

[sonaljain123]

Hehe thanks Gina, I am sure you are busy too...

 

[GinaWhipp]

Finally, some free time... I am looking now!

 

[sonaljain123]

Great, I am online, you can let me know in case you have any questions...

 

[GinaWhipp]

Okay, I see the problem, you are trying to match column names to table names and that won't work.

Excel is a flat file format and Access is a normalized database that can show a flat file format. So, Karts, Levels, Sublevels and GL Accounts go into their own tables. Then you have *joiner* tables that bring the seperate entities together... tbkKartGL and then the balance the brings it all together tblKartRollUp. You place the appropiate data in those tables and then use a form and/or report to display it the way you want with subtotals and totals.

 

[sonaljain123]

Yes that is correct. I can make karts, levels, sublevels, GL Accounts into separate tables but then how can I link them so that when I click the plus sign I can get all information regarding that?

 

[sonaljain123]

So is there a better way to do this?

I can have following tables:
table 1 Levels
levelid (PK)
level value

table 2 Sub Levels
sublevelid (PK)
sublevel value

table 3 GL_Accounts
date
glaccountnumber (PK)
glaccountbalance
glaccountshortdescription
glaccountlongdescription

table 4 balanceaccounts
balanceaccount (PK)
balancedescription

and last can be the joiner table may be..

does that make sense?

 

[GinaWhipp]

Okay, you are not going to get all that in one table, that is what forms and/or reports for. If you really want to do all that in a table then I'm at a lose... Databases don't work like that. Tables are like buckets of water, you can't really seperate the water into that many levels.

 

[sonaljain123]

Okay...I understand that, can I just arrange the tables in such a way that I can see which GL Accounts fall under BAL_ID and the corresponding GL Account Balance?

Would you be able to lay down the table names and columns for me?

thanks!!

 

[GinaWhipp]

I would be using Excel not Access for this task. There are templates available in Excel at Microsoft's site. Access is not suited for what you want to do. They may have told you to use Access but they may be looking at those *little plus signs* and think that will give them what they want but it won't. Tables are not (and were not) designed for that.

You can't do that with the tables (reference your last post), you can do that with forms (or reports) but not tables. Go look at the templates for Balance Sheets on Microsoft's site. Again, the only thing that can close to what you want has to be done via forms and/or reports.

 

[sonaljain123]

I understand your view point...I think what I can create are views if not exact tables with all the information possible.

So lets say I can see the accounts in each BAL_ID in one with values in another may be..

Do you think that is possible? I am sorry but I have to get this in access only..I dont have any other way..

Also is it possible in access to see result of a query in an unsorted manner?

Thank you for your help

Sonal

 

[GinaWhipp]

I don't understand this statement...

So lets say I can see the accounts in each BAL_ID in one with values in another may be

...are saying you want to see karts and their balances in one table? If yes, then you cannot add in tables like you do in Excel. (Can't do that in queries either.)

 

[sonaljain123]

Hi Gina,

What I meant was to see to be able to link Table 2 to table 3. So what i can do is have each GL_Account mapped to BAL_ID? That is possible right.

 

[GinaWhipp]

not without some kind of relationship... you would need to put a GLAccount field in table 2 or the sublevel in table 3. They need a way to *talk* to each other.

 

[sonaljain123]

Hahaha its funny thing to say but I thought about it too but dint bring it up respecting the decorum of the forum...

I am open to any means of communication....just let me know...

thanks!!

 

[GinaWhipp]

I would try that and see if it gives you what you want... Having not worked with subdatasheets (the little plus sign) because I have no use for them I'm unsure of the results and whether they will give you the desired results.