Can I do this - N00B questions - please help

[Debbie_P]

I am working my way through learning Access, but am very new at it. I have an idea of what I want to do, now I just need to know if I can



I have one table with a list of schools, each school has a unique name and has an individual number, which is the primary key right now.
I have another table for tracking incident reports by date neither table is related to the other right now...that being said.

I would like to create something, which I can track a number of items sent to each school by date. for example. I would like to have an entry form where I can choose the school name from a drop down then enter the date and # of pens, # of pencils, and budget code.

Then do a report showing School Name, Month/yr, Summary of Pens, Summary of Pencils

So what do I do to use the school name from one table then have it record individual records and summarize on a report with the school name only showing once, the month, and the total of the monthly entries.

I know I'm probably overthinking this, so I'm hoping someone can help, I have like 160 schools and do not want to create a table for each school

Thank you to anyone that can help me

[June7]

What are the incident reports associated with if not schools?

Have another table for tracking 'transactions' of distributing supply items. Do you have a table of supply items to choose?

This is called a many-to-many relationship. Each school can have many supply items and each supply item can associate with many schools.

Conventional options for data entry would be:

1. one form bound to the third table with comboboxes to select supplier and product

2. main form bound to Suppliers and subform bound to third table with combobox to select product

3. main form bound to Products and subform bound to third table with combobox to select supplier

Review https://support.office.com/en-US/Art...rs=en-US&ad=US. No code required.

[Debbie_P]

The product is the same every time, it just the amount that changes

So do I create a table that has school name that I can pick up from table one then just create a new record for each date with the items?

Thank you for your answer

[June7]

What do you mean the product is the same every time? You have pens, pencils, paper - those are different products. Consider:

tblSchools
SchoolID
SchoolName

tblProducts
ProductID
ProductName

tblTransactions
TransDate
SchoolID
ProductID
Quantity

[Debbie_P]

What do you mean the product is the same every time? You have pens, pencils, paper - those are different products. Consider:

tblSchools
SchoolID
SchoolName

tblProducts
ProductID
ProductName

tblTransactions
TransDate
SchoolID
ProductID
Quantity

Okay, so I have the first table with all the school id information, do I make the other two tables relational to the first table? Or the third table relational to the tblschools & tblproducts?

thank you for all your help

[orange]

Here is a tutorial that will show you how to design tables and relationships to support a business description.
You will save yourself a lot of frustration, learn relational concepts and speed up your learning by working through the tutorial.

The key is to learn about database, then learn about Access.

Good luck.

[June7]

As denoted by the primary and foreign key fields, the first two tables are related to the third table.