Access beginer question

[neven]

I just registered on the forum and this is my first post.



I'm total MS Access beginer but I do have some average knowlege about databases and I'm familiar with terminology.

My quest is this:
I want to create a database which will store my expenses, each defined by several parameters and then I want to be able to make reports on each of those parameters.
To be more specific, i will recieve an invoice on some sum, on some date (month, year), from someone, and i also need to assign a category for it and 5 levels of subcategorys.
Where do I start? Blank? Template?

I purchased MS Office 2007 Pro so I'm using Access 2007.

Thank you.

[jbarrum]

what you want to do sounds simple enough but the first thing you must do is design your db on paper (what tables are needed and what data those tables will hold). At first glance you will need the following tbales:

Vendors (vendor ID: PK)
Invoices (vendor ID: FK) (Catgories ID: FK)
catogories (Catogorie ID: PK) (Sbucatagory ID: FK)
subcatagories (Subcatagory ID: PK)

The create your relationships between each of the tables.

[neven]

what you want to do sounds simple enough but the first thing you must do is design your db on paper (what tables are needed and what data those tables will hold). At first glance you will need the following tbales:

Vendors (vendor ID: PK)
Invoices (vendor ID: FK) (Catgories ID: FK)
catogories (Catogorie ID: PK) (Sbucatagory ID: FK)
subcatagories (Subcatagory ID: PK)

The create your relationships between each of the tables.

Thank you for your reply.

I did start with creating of Tables. I have no questions about Vendors table (in my case it is PR_Agency table). That one is clear to me. I have Agency_name, Agency_Address columns in that table.
Questions start now
I'm trying to track (and analyze) marketing costs on few categorys for each product (ex. orange juice[product]--->drinks[parent category 1]--->supplierXY[parent category 2]). So when I recieve 100$ bill from Pr_Agency for Ad on local TV for my orange juice I have to assign them to the Orange juice and that should imply that 100$ is also gone for Drinks (parent 1) and SupplierXY (parent 2). Is that my second table? With Item_name, Item_category, Item_Supplier columns?

Those 100$ spent on Orange Juice Ad should also be asigned to the second group of categorys and that would be Access TV[media name]--->TV[media type]. Is that my third table? With Media_name and Media_type columns?

Just to make sure I'm not misleading you, I will need reporting on all categorys (ex. show me the % of total marketing money paid to ACME Advertising agency for marketing in '09 for advertising drinks on TV).

Thanks for helping me out.

[jbarrum]

you are on the right track but you will need more then three tables. From what you have told me you will need the following tables:

1) [Paid_adds] holds your paid advertisments

2) [Suppliers] 1 to many => [paid_adds]
3) [products] 1 to many => [Suppliers]
4) [product_catagory] 1 to many [products]

5) [Pr_Agency] 1 to many => [paid_adds]
6) [Media] 1 to many => [PR_Agency]
7) [Media_Type] 1 to many => [media]


Once this has been done then you can create queries to total your add dollars by PR_Agency, Product, etc....

I can help you with the reprots and queries once you get to that point.

[neven]

you are on the right track but you will need more then three tables. From what you have told me you will need the following tables:

1) [Paid_adds] holds your paid advertisments

2) [Suppliers] 1 to many => [paid_adds]
3) [products] 1 to many => [Suppliers]
4) [product_catagory] 1 to many [products]

5) [Pr_Agency] 1 to many => [paid_adds]
6) [Media] 1 to many => [PR_Agency]
7) [Media_Type] 1 to many => [media]


Once this has been done then you can create queries to total your add dollars by PR_Agency, Product, etc....

I can help you with the reprots and queries once you get to that point.

I did exacty what you sugested and now have 7 tables. I even made a Form for [paid_adds] . I did encounter some problems.

My tables 2-4 now look like this:

2. [Suppliers] 1 to many => [paid_adds]
3. [product_categorys] 1 to many [paid_adds]
4. [products] 1 to many => [paid_adds]

The thing is my [product] is always in same (one) [product_category] and that [product_category] is always in same (one) [Supplier]. So I guess my 2-4 should look something like this:

2. [Supplier] 1 to many => [product_categorys]
3. [product_categorys] 1 to many => [products]
4. [products] 1 to many => [paid_adds]

I have no idea how to do this. Thats why I did it like above. Now when I'm entering data in my [paid_adds] table i need to enter all 3 ([Supplier],[product_category] and [product]) yet I'm almost sure that there is a way I can enter only [product] and since it is defined by [product_category] and [Supplier] I'll still have a report/query ability on those two parents.

I did section 5-7 similar to how I did 2-4 so if you help me figure out how to do it proper I think I'll manage to fix those too


Thanks.

[jbarrum]

It looks like you have gotten a littel off track but you are close. I guess my first question would be why did you steer away from the relationships that I difined earlier.

Look at this like a pyramid. At the apex of the pyramid is your [PaidAds] working down from their you have your Suppliers. Suppliers can appear many times in Paid Adds so this would be your first one to many relationship [Suppliers] 1 to [PaidAds] many. Make the primary key of suppliers a Forign key in PaiAds.

Next question would be (since each product catagory can have many products) can each supplier supply more then one product or product catagory?

Note The way that you metioned your relationships work above

2. [Suppliers] 1 to many => [paid_adds] THIS IS CORRECT
3. [product_categorys] 1 to many [paid_adds] THIS IS NOT CORRECT
4. [products] 1 to many => [paid_adds] THIS IS NOT CORRECT

I would disregard your proposition that followed all together.

If you follow my original design you should be fine. If you want to upload what you have done so far I will be happy to take a look at it.

[neven]

I guess my posts weren't clear enough so thats why we have this disagreement. This [Supplier] wasnt a good choice of naming it and that is causing confusion. Lets call it a [Brand] now.

[Paid_adds] is not directly related to [Brand]. Expenses in [Paid_adds] are not made for [Brand] but for [Product].

To clarify a bit more I'll rename [Product_category] to [Sub_Brand]:

[Brand]......[Sub_Brand].........[Product]
BREMBO.....BREMBO BRAKES.....BREMBO BRAKE DISK 15"
..........................................BREMBO BRAKE CALLIPERS XX
................BREMBO WHEELS....BREMBO ALLOY WHEELS 15"
...........................................BREMBO ALLOY 17"

So when I recieve $100 bill from [PR_Agency] it is for specific product. And one product only. Lets say it is for BREMBO BRAKE CALLIPERS XX. And another $200 is for BREMBO ALLOY WHEELS 15".
Isn't enough just to have [Product] one-to-many [Paid_adds]? And each product defined by both [Sub_Brand] and [Brand] in other table(s)? So when I enter those $100 and $200 in [Paid_adds] I only need to select [Product] and still have ability to report that [Brand], in my example BREMBO, spent total of $300 on advertising? From which $100 was on [Sub_Brand] BREMBO BRAKES and $200 on BREMBO WHEELS? And ofcourse expenses for each [Product] alone.
Is my logic wrong?

[jbarrum]

I do not think that I am confused but take a look at the file I have attached and tell me if this is basically what your query would look like.
Move around in the file and notice my validation tables.