We need to access data from an old access database but the front-end was a program we do not have access to or it won't meet our needs. I've spent the better part of a day trying to figure it out and I think I understand how it's set up. The Associations table has no primary key so I believe it uses two keys to uniquely identify an item or to tie items together. tbContent seems to hold the details of items while tbSections is the grouping or categories of the contents. If you look at the data it looks like the site is laid out in that table.
Each item has an ID key and a ParentID and a title. The very first Title is Main site and it's ID-Parent ID is 0-0. The next item is Clothing and Equipment and it's ID is 1 and it's Parent ID is 0. It continues this way until 6 so it looks like 1-6 are subcategories (I think this is where LevelNum comes in) under the Main site. Then lower in the list Title Other has an ID of 18, Level Num 2 and ParentID is 1. That tells me that Other is a subcategory of Clothing and Equipment. This is easy but as the list grows it just gets way more complicated. For example, title Life Cycle Costing has an ID of 1348, Level Num of 3 and ParentID of 1625! So if I wanted to see where it falls under the Main Site I'd have to take it's Parent ID 1625, find the item that has the ID 1625 then find it's Parent ID and use it to find the item that has it as it's ID, etc....etc...etc.
I need to make a report in Access that will allow people to navigate the lists and find the details. So they'd go into Clothing and Equipment and then into Other and so on. The only query in the database is
SELECT tbLoadListItems.ItemID, tbLoadListItems.Description, tbLoadListItems.Weight, tbLoadListItems.Custom, tbLoadListItems.Status
and there were no relationships set up except between the two tables included in the query but looking at the tables I've created the relationships myself and include it below. The one confusing part is that Parent ID in tblSections appears to be referenced twice in the tbAssocations table.
I haven't worked with Access in a long time and quite honestly I found it difficult to create the report I wanted. I'd rather create the sql queries and have a report be based off of those but I'll take any help I can get on this. We'd rather not have to manually extract the data from the database plus this information could be useful for future projects.
So I'd like to create a report that will allow the user to drill-down from the Main site and be able to print or export a list of a category, such as all Clothing and Equipment or a list of everything under Other (which is under Clothing and Equipment) and get the details for those items as well as the details of the tbLoadListItems (not sure where they fit in, they may need to be a separate report). Any help, suggestions, insights are greatly appreciated!!!