How to organise data where it has 1 to many relationships

bluue s
bluue s used Ask the Experts™
on
I have data that are 1 to many relationship.
For eg, The book with the same title can be in multiple locations and i would like to find out the information related to the user borrowing in multiple locations - both the user information + the respective multiple locations information.

How do i organise the data in excel so that i can still analyse and also able to extract data easily ?

Prob now is for eg. i have a row but also multiple row that is pertaining to the same book title so it would be very misleading to count the number of rows to get the total number of books borrowed since some rows are like "repeated" and not the actual unique data.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
IMHO you are using the wrong solution for this problem. A database would be better
you  have a book say "My book" , you have several locations (loc1,loc2, loc3, loc4
You have a total of 15 copies of the book.
loc1 has 5 available
loc2 has 1 available
loc3 has 0 available
loc4 has 0 available
sum location(s) available and subtract the result from total available to get # of available books
15660
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I would agree with David, you are trying to use a screwdriver, when you need a wrench.  Use Access for this type of thing, not Excel.

Dale

Author

Commented:
ok so meaning if i want to have pivot charts like in excel withe trend lines etc, i would need to link the access db to excel ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
What you are talking about are relational database concepts, you have many books, and many locations, and any book may or may not be located in any location, so in Access you would need a Books table, a Locations table and a BookLocation table to track what location each book is in.

Access has the ability to create reports and queries which would help you visualize where each book is located, in a grid, if that is what you are looking for.  Or you could create an Access query and push or pull the results of that query into Excel.

Author

Commented:
Has anyone tried this https://www.opengatesw.net/index.htm 

I am just thinking if anyone has tried this software and how's the review like from a user point of view (genuine not those marketing gimmicks). Thanks.
Rob HensonFinance Analyst

Commented:
pivot charts like in excel withe trend lines etc

How are you planning to chart the location of books?

For example, you could have a chart with Book Title along x axis and quantity up the y axis. Each location would then have its own entry for each book. This would look ridiculous if you're talking about hundreds of book titles.

With Pivot Chart I guess you could filter on Book Title so that you're only seeing data for one book and a column for each location.

Why do you want trend lines?

Author

Commented:
@Dale: Ok, meaning if i need to know who are the profile (demographics) of the users who borrow the books, usually which location is most popular, what books are popular with the people, when is the time where people borrow the most books , I can push the data from Access to Excel to run pie charts, trend lines etc ? I need visualization of data, not use the data only.

Meaning one needs to have a certain level of Access knowledge to run the query. Is this correct ?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Bluue s,

Yes, one needs to have a certain level of Access knowledge.

Where, how is this information being stored now?  If it is being stored in Excel, you obviously have some really large excel spreadsheets with lots of redundant data.
Commented:
I will explore MS Access.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial