Filtering Forms to Display Specific Data

Question

Hi all,

I am new to Access but have figured out how to do simple things like tables, forms, and buttons. I have multiple tables:

-Client ID which has ID #, First, Last, or Business Name and Entity

-Tax Years has a list of years from 2000-2021

-Engagement Letter which has a one-to-many relationship with Client ID as well as Tax Years

-Scanned Tax Returns which has a one-to-many relationship with Client ID as well as Tax Years

-Tax Set-Up which has a one-to-many relationship with Client ID as well as Tax Years

-Tax Organizer which has a one-to-many relationship with Client ID as well as Tax Years

-TR Processed which has a one-to-many relationship with Client ID as well as Tax Years

I already have a form that you can enter the client ID or name, hit the search button and a new form will pop up. The new form has ALL the tables as subforms so I have a summarized view of what has been done for the client. After I hit the search button, the new form auto-populates the most recent record of the client in each subform. Right now if I want to see different tax years then I have to hit the Next Record button on the bottom of the subform. How can I filter by ID number but by Tax Year as well?

Answer 1

So I have a Search Client Form where you can search by Client ID, First/Last Name and once you fill in one of those boxes then the rest will auto-populate with the correct info. Once the info is in, I have a search button that then opens up my Client Summary Form. The subforms are all linked to the Client Summary form by Client ID. Should I first make the Client Summary form continuous? How do I create a second link? And in the combo box would I just create my own list of tax years when creating it?

Update: When I added the Tax Year combo box to the main form then went to one of the subforms to link the master fields, the combo box doesn't show up as one of the options.

Answer 2

OK, you don't need the table or any relationships. The tables are all related to the Client and that's all you need.

The fact that there may not be records in some tables for a tax year does make sense to want to filter by tax year.

So the question becomes whether you want users to always filter by year or not. If yes (and it makes sense to do it). You should be able to set up a second link for each subform between the Year field and the combobox.

If you want them to see all years, that's more complicated.

Answer 3

Thank you both for a swift response!

Again, I am new to Access so I thought that having the Tax Years table would help because then all of the years in the other tables would be connected to one constant. Should I delete the tax year table and just create relationships between Years on all tables? If so, what kind of relationship? Also, how do I link the combo box to the subforms?

My reason for wanting it this way is because right now the most recent data shows up so if I have only 2019 for Engagement Letters and only 2020 for Tax Set-Up then the two different years would show up. I want it to be so that when I search for Client A and Tax Year 2020 then the respective data shows up and if there's no Client A, Tax Year 2020 for certain tables then it will show up blank and data can be filled in.

It will also help make it more user-friendly for some of my coworkers because it would lessen the chance of them viewing the wrong tax year on a certain table and making edits unknowingly. This database is more for Admin rather than our accountants so we don't view previous year's records often. The logging of data on all these different tables are more to cover our bases if something goes missing or a form doesn't return signed, etc.

Thank you again!

Answer 4

I'm confused as to the value of the Tax Years table. Its seems to me that its totally superfluous. Each of your other tables contains information about the work done for a client. Each of those tables should have a field that indicates the year the work was done. So what purpose does the Tax years table serve?

As far as seeing the different years, I would use subforms in continuous form mode. It sounds like you are using Single form mode. This way you can easily see all the years records.

I would also agree with Tom that the ability to scroll through the different years records is an advantage.

But, If you want to pull up only a specific tax year. I would include an unbound control on the form with the subforms. I would then create a link between that control and the subforms.

Answer 5

I'm confused. On the one hand you say it auto-populates the most recent record (I'm interpreting: to the exclusion of others), yet you also say that you can navigate to previous years' records.

To me the latter seems a benefit: we are usually working on the current tax year, but sometimes want to scroll back to previous years.

It seems you want to show only 1 year. Then that benefit goes away.