Newb question

[blkdragon201]

I'm brand new to databases and to Access. I've used excel in the past and that's as close as I've come to microsoft access related.

I was wondering how I should setup my table/database to keep track of who has what items.


Here's the outline: I have about 400 different items that my customers can buy from me. I also have about 30 customers that can buy those items from me. I will probably keep adding customers into the database as time goes by. The way I want to use this database is to keep track of which customers has which items.
I was thinking of just adding all 400 items as separate IDs? and then also setup customers in the fields. So every time a customer buys an item, for example, item ID: 108, i want to just pull up the file for 108 and add the customers name to that item ID.

Would this be the best way to set up my database? I also want to be able pull up reports such as, "list all customers that have item 108 in stock".

Like I said, I have little to no experience with Access. But I am a fast learner so please, any input will be appreciated.

[RuralGuy]

I would start by looking at the Templates that come with MS Access and that MS Office has on-line. See if you can find something close to what you need that can then be modified to better suit your needs.

[Microsoft Access Database]

You need a table for items and another for customers. Assuming one customer can buy many items and vice versa, you will need to create a many to many relationship between the tables. You do this by creating a new (third) table. Enter the primary keys of both tables in this linking table. A query will automatically pull up the join you created between the tables and you will be able to return your data easily. This will be your starting point.

[blkdragon201]

So I need two separate tables. In one table I should have items.. But what do I put into the table fields? Am I supposed to put the Item IDs into the fields or do i have 1 field that says "item ids" and enter each item id as an entry under that field? Same question for the customer table. Like i said before, I have no experience. If you could just point me in the right direction (maybe videos or tutorials online?) I appreciate it.

[banjo1t]

So I need two separate tables. In one table I should have items.. But what do I put into the table fields? Am I supposed to put the Item IDs into the fields or do i have 1 field that says "item ids" and enter each item id as an entry under that field? Same question for the customer table. Like i said before, I have no experience. If you could just point me in the right direction (maybe videos or tutorials online?) I appreciate it.

You would need to put the item ID's as you say in it's own field, and list the items below this, as you probably need to add price fields, item number fields etc as the database grows....

Similar with customers, you will need tel number, Address, etc

[blkdragon201]

You would need to put the item ID's as you say in it's own field, and list the items below this, as you probably need to add price fields, item number fields etc as the database grows....

Similar with customers, you will need tel number, Address, etc

To make things simple I don't need any fields to go along with the Items (such as price, description of item etc..). All I need is a field for the item ID, that tells me what item i'm looking at. Same goes for the customers..I don't need any contact info, just the customers name. So Given this circumstance I thought I could just make one easy table. One field with item IDS, the rest of the fields are going to be customer names, and under each customer field i'll have a checkbox that is either yes/no. So when i go through the table i can see if they have the item or not. Will this work?'

Also if i set it up the way i said earlier. How can i make reports to show me, for example, if i wanted to see who has item ID: 123? I want it to pop up all the people with a yes check under their field for item 123.
And another report that shows me what item a specific customer has?