Selecting certain records and showing them a certain way in Access

Hello all.  

In the end I have my work and learning cut out for me with Access.  I know databases some in general and know what I need to do but I am unsure how to do it in Access, and maybe even needing some VB.

The Project Outline
This project is for a tree logger.  I will not get to deep into the project, only what is needed for my current task.

Need
To show the data in this fashion.
1.  Have a drop down menu to select the project number or name
2.  Then after selecting the project number, a list of trees and their logs show grouped by the tree type.  For instance.......

Project 1
  Tree 1
    Log 1
    Log 2
    Log 3
    Log 4
  Tree 2
    Log 1
    Log 2
    Log 3
  Tree 3
    Log 1
    Log 2

etc.

Here is a picture of my database thus far.
Database thus far
How do I make a form to select the records from the Logs database and use a variable from the drop down arrow for the JobID, that will then only list records for the JobID?  Then how do I get a list format to show the records?

I hope I explained that well enough.

Thank you for your help.
getwidth28Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
The basic technique is called Cascading comboboxes.
Here are some links to investigate:
http://fontstuff.com/access/acctut10.htm
http://www.utterangel.com/UtterAngel/utterangel.aspx?cat=acc
0
Jeffrey CoachmanMIS LiasonCommented:
Just note that the exact output you are looking for, will not be possible in a listbox directly, it can be sorted by the Tree tough...

Very basic sample attached...
Database168.mdb
0
Jeffrey CoachmanMIS LiasonCommented:
You can get the output you want in a Report though...see the new sample..
Database168.mdb
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jeffrey CoachmanMIS LiasonCommented:
You may want to take a minute and stop to evaluate your design here...
1. Try not to name all of your Primary Keys "ID", ...it becomes confusing figuring out what table they belong to.
2. Try to enforce referential integrity in your relationships where needed.
3. I don't see a need for the Job table to be joined to the Logs table...
4. AFAICT, you also need a "Tree Table"
5. Not sure how the "Project" fits in with your design there? 9I don't see a Project table?
...
There are other concerns as well, but I don't have all the details, ...so you may want to post a question on the design/relationships...

In short, before worrying about the form interface, you should really make sure the design is rock solid *first* (else you will have to constantly re-design the form as the table design changes)

So take what I posted as a general guideline...


JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
getwidth28Author Commented:
Great stuff.  My Jobs table was like your Projects table.  I am a novice at best with database design, so I'm sure that it won't be at a best practice level at any time for my work.  

Great notes.  

On the primary key I just used the default.  I see where you have no primary key of ID but as you said your made your own.  

If this information helps.  He wants to use a portable access device like an iPad with the access app and enter his logs and trees into the database.  

He will have the iPad in hand at the time of looking at the tree.  He will press a button for "New Tree", then enter in multiple logs "New Log".  Then after that tree, he would go to the next tree and push the "New Tree" button, then enter in the logs for that tree.

I guessed I asked for this report first hoping that it would be easier to figure out/see how it was made so I could modify it how I need/etc.
0
Jeffrey CoachmanMIS LiasonCommented:
I see something like this as a start:
relationships
0
getwidth28Author Commented:
How did you make the "+" thing?

Plus thing
0
Jeffrey CoachmanMIS LiasonCommented:
It happens automatically when you set the relationship.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.