Auto Organization?

[ProjectCamaro]

I'm a first time Access user but was requested to create an inventory list from my boss.

What I'm doing is having a database that has all the different inventory items and it creates an individual form for each item along with a picture of said item.
I'm also exporting the database into Excel to have a nice spreadsheet to look at that lists all the items we have. My problem is I would like to seperate the items by their location. So everything in location A wil be grouped together and so forth.


Is there a way I can have Access do this for me?

[jzwp11]

Welcome to the forum!

What I'm doing is having a database that has all the different inventory items and it creates an individual form for each item along with a picture of said item.

You would only need to create 1 form. You would step through each record using the record navigation buttons on the form.

I'm also exporting the database into Excel to have a nice spreadsheet to look at that lists all the items we have

You can use a form in datasheet or continuous form view to acheive the same look as a spreadsheet.

My problem is I would like to seperate the items by their location. So everything in location A wil be grouped together and so forth.
Is there a way I can have Access do this for me?

This is easy for Access, provided your table(s) are set up correctly.

Speaking of tables, setting up the tables is the most important part of any successful relational database.

With that said, you say that a location has many items, so that describes a one-to-many relationship. Now a question, can the same item be held in multiple locations?

[ProjectCamaro]

This is easy for Access, provided your table(s) are set up correctly.

Speaking of tables, setting up the tables is the most important part of any successful relational database.

With that said, you say that a location has many items, so that describes a one-to-many relationship. Now a question, can the same item be held in multiple locations?

First off thank you for your reply and welcome!

No, the same item cannot be held at multiple locations.

[jzwp11]

OK,

First, I would create a table that holds all possible locations. Each location would be a record in this table

tblLocations
-pkLocationID primary key, autonumber (pk=primary key field)
-txtLocationName (txt=text datatype)

Then in your main table, put a reference to the location

tblItems
-pkItemID primary key, autonumber
-fkLocationID foreign key (fk=foreign key) to tblLocations

You will want to go to the Relationship Window and create the relationship between the two tables via the primary key->foreign key relationship.
i.e. pkLocationID-->fkLocationID

Be careful not to use lookups (combo/list boxes) in your tables as they can cause problems as indicated on this site. Combo/list boxes are best left for use on forms.

You can now create a query that orders/sorts the items by location. You would go to the design view grid add the two tables, add the fields you want to see in the lower grid and sort by location.

[ProjectCamaro]

Thank you for the reply. I'll take your advice and hopefully it'll come out.

[jzwp11]

You're welcome. Please post back with any questions.

[ProjectCamaro]

Awesome that work perfectly.

Finally how do I create a query that sorts the items by location?

[jzwp11]

From the ribbon, Create-->Query Design. Add both tables. In the lower grid, select the fields you want displayed. For the location field, go to the sort row and select ascending or descending.

[ProjectCamaro]

Everything appears to be working correctly now. Thank you so much!

[jzwp11]

You're welcome. Good luck with your project.