Need ideas on how to organise table..

[Calvinle]

Hi,

I usually use excel VBA to organize data, however, due to access properties of form, I want to switch to access. But I don't know how should I do to organise the data so it respond to my need or how many table that I will need.
I have category, country (buyer), and % (example):
Dog:
Asia - (50%)
Russia - (50%)

Cat:
America - (20%)
Canada - (20%)


Asia - (60%)

I have more than 100 different categories. And the thing is, I want to be able to do so:
Having a listbox, if user select CAT, it will list all the country and their %. Also, if another country buys the product and they are not on the list, they can be added as well.

Please help.

[ssanfu]

Here is a tutorial to help you get started:
http://www.rogersaccesslibrary.com/forum/forum46.html

After you have gone through the tutorials, use pencil and paper to design your tables, and forms.
Only then start creating tables, queries and forms.

[Calvinle]

I have already create something with listview, and form on access. Being using it since a week.
Like you mention, use a pen and paper and write down, but that is the problem. I don't know if I should make them same ID?
Table 1:
ID - Category

Table 2:
ID (same ID as Table 1 so it can relate the data) - Country? But how about many countries?

Table 3:
I suppose the same as Table 2 as the % is related to the country.

[ssanfu]

You have said what you have done, but you haven't explained (described) what you are trying accomplish. Maybe give a few examples of the data.

Did you work through the tutorials?

What is listview?

"ID" is not a very descriptive name.
For Table 1 ("tblCategories") I might use "CategoryID" and have the Type as Autonumber - primary key (PK)
If Table 2 ("tblCountries"), I would have "CountryID", type Autonumber - primary key (PK)

Table 3 would be named maybe "tblTransactions" (??).
"TransactionID", (Type Autonumber - primary key (PK))
Then fields"CategoryID_FK" (type Long)
"CountryID_FK" (type Long), and
"Percentage" (Number - Single??)

Use only letters and numbers in object names. No punctuation or special characters (underscore is the exception).

BTW, the PK field should never be visible.

------------------
I'm just spitting into the wind here, because I don't know anything about your project.....

[Calvinle]

Here is a sample of what I really need, but since i don't know how to organise that table, I can only provide the form.

Request.zip

[ssanfu]

Did you work through the tutorials?

The relationship between the two tables was wrong. I don't know your ideas on what the form is supposed to do, but I added another form that I think is easier to use because Access takes case of displaying the linked records. No code involved.

[Calvinle]

Hi!

I went throught the first part, not all. It's long..!

But for this project, that's exactly what I need! However, when I go to the Edit form, and try to Add or Remove, how can I make it so if the Teacher is not in the list, they can choose to Add New Teacher, or Remove That selected Teacher.


Here I attach the edited one, and if you can help me to add new teacher or remove teacher that will help a lot..


Request1.zip

Thank you!

[ssanfu]

You shouldn't delete a teacher because you will have orphan records. Use an "Active" field to display not display a teacher.

To add a teacher on the fly, use the not in list event.

I added the not in list event code, but you need to understand what the code is doing.
Google "notinlist Access 2010"