QueryReport w Asset Format - How To Track, Organize, and Log Asset Inventory With Parent Child Relationships in Microsoft Access


	Back to Asset Management Lessons Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

QueryReport w Asset Format

Nathan Schmidt

4 years ago

I've been playing around using this type of asset management setup. In my database, I want to be able to use the Parent and Child data from the Asset table to create a report showing a specific Asset selected in a query, in this case lets say a server is my parent asset. I then want the report to have all the servers listed as parents and then below each server have the child assets installed within that asset (NIC, Hard drives, modules, etc.)I can't seem to get the right criteria in the query to display the correct assets. I always get the parent assets but can't seem to get the children that are associated with those assets. On the main Assets form these children are displayed within a subform on the main assets form. If you have a lesson that shows how to make this work or could explain it to me it would greatly help me out. Thanks!!

	Juan C Rivera @Reply 4 years ago
Hi Nathan have you looked at the Asset Management Seminar? I think that may help look in tech help Richard did a video on components was some time ago and also review the relationship one to many and sub-form setup. may list the parent in the main form with parts in subform'just a thought.

	Richard Rost @Reply 4 years ago
You may also want to check out Self Join Relationships

Nathan Schmidt OP @Reply

4 years ago

I have watched that and that's what i am referring too. I used that as a baseline to start my DB. Problem is i don't know how to set up a query using that setup get get what I'm looking for. I'm tried many different ways but I'm not sure if its something that can even be done or if if needs to be done in VBA or SQL rather than using the default query design. I have many other queries that i use for reports but can't see to get the Parent listed with there children in a query.

	Nathan Schmidt OP @Reply 4 years ago
Richard, I'll check that one out now thanks!

Nathan Schmidt OP @Reply

4 years ago

Richard,
I have watched that in the past but that is a little different than the way you teach that in the Asset Management class. I do have all that functionality working in the form and sub form. My main asset form shows the parent asset and the sub form shows the children of that parent. My problem is trying to get them to show up on a report that way. I'm having issues selecting a set of parents with a query and getting there children to appear in that same query so i can use that data to create a report. I'm guessing that is because i would need to have the record open in the form to see its children but i can't have all the "parents" of a certain type open at the same time. Hopefully I'm making some sense lol

	Juan C Rivera @Reply 4 years ago
Oh got it . Try groupings check out the video on setting up groups in a report. Access expert level 12 https://599cd.com/site/courselist/access2013/expert/x12/

	Richard Rost @Reply 4 years ago
It's been so long since I recorded Asset Mgmt... not sure if I used self-joins in there or not. It's all based on what YOU need to have in your database... that's why you build your own. :)

Nathan Schmidt OP @Reply

4 years ago

Juan, Thanks i know that part too lol. I'm stuck right before that. I need a query that lists parents and children so i can manipulate them in the report using the grouping and sorting. My queries only return the parent assets i'm filtering but i'm looking for those and the children associated with the parent. I'm not sure i can even do it without having the records open but i can't open multiple records at the same time to locate all there children.

	Kevin Robertson @Reply 4 years ago
Try using a Subreport for the child records. Subreports are covered in Access Expert 9.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Asset Management Lessons.

Next Unseen

New Feature: Comment Live View