Solved

Trying to build report on old Access database

Posted on 2014-11-27
7
157 Views
Last Modified: 2014-12-11
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
FROM tbLoadListItems;

Open in new window

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!!!

relationships maptbAssocationstbContenttbSectionstbSections again lower in the listmore tbSectionsand even more tbSectionstbClimates - not sure where this fits intbJobs but don't think this is relevanttbLevels but don't think this is relevanttbLoadListItems - this i will needtbLoadListstbOrders but don't think this is relevanttbTypes but don't think this is relevanttbVersion but I really don't think this is relevant
0
Comment
Question by:HSI_guelph
  • 4
  • 2
7 Comments
 

Author Comment

by:HSI_guelph
ID: 40469256
Also I made out a MindMap to help visualize what is going on so I'll put it here too.  I think I was scared by the big numbers but I think the entire site has only 4 levels .  

Mindmap
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40469674
Just a note: I don't like the doubled relation between Associates and Sections.

/gustav
0
 

Author Comment

by:HSI_guelph
ID: 40470410
Believe me I know how you feel!  I would have created a separate table for headings but that is just me :)
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 40470418
But for this to work, both IDs from Associates must always match the same parentID. That doesn't make sense.
Also, you have a double link to Contents. The Title join should be removed.

/gustav
0
 

Author Comment

by:HSI_guelph
ID: 40470775
I believe now that the tbAssocates table is not required in my report.  I think all I need is the tbContents and tbSections tables.  I need the tbSections table to group and categories the data in the tbContents table.  The other tables may have had roles in the program that was originally written to capture and view the data but is irrelevant to what I am trying to accomplish here.  

This is the results of my new query (I am making my queries in SSRS and then copying the code to Access because I am much more familiar with SSRS).  As you can see they've nested the data into one table and by taking the SectionID in tbContent I find the Title and ParentID in the tbSections table.  Then I take that ParentID and find where it is in the tbSections table to find it's ParentID and then the ParentID of that until I end at the Main Site.  
results of new query
What I would love to have is a report with a drop-down menu that lets someone pick a subheading off of Main Site, such as Clothing and Equipment or References.  Then I'd like a second drop-down to populate from that choice and let the user pick a subcategory under that.  Such as picking Clothing and Equipment, then picking Headwear.  You might have a subcategory under Headwear but some items might appear there.  So I almost need to have a table that when you get to Headwear you get results showing up that are under Headwear.  But you also get another drop-down box to go a category deeper and if you do then the table displays the contents under that heading.  So as you pick sections there will be some results and then more choices to go a section down.

So for now I've got an excel sheet for them with everything on it until I can figure out how to set up multiple drop-boxes.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 40474484
I know people pan it but a table with this structure is exactly how the Switchboard form works.  I made a replacement that works as a continuous form rather than hard-coded buttons.  it may give you an idea for an interface.
Switchboard-Simple141201.zip
0
 

Author Closing Comment

by:HSI_guelph
ID: 40494127
Manually set the parameters in the query and just copied the output from SSRS sql query designer into an excel sheet for them to use.  If the work results in a billable project I can put time into making a usable interface for them but for now they have what they need.  Thanks for the replies!!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now