Colin's table is just that, a table showing the different types of queries - you can lookup against it to find the appropriate values.
Or you can use this query for your particular requirement
Note that hidden relates to queries hidden by right clicking on the query in the navigation window, selecting object properties and then ticking the Hidden attribute. It does not relate to right clicking on the query in the navigation window and selecting 'hide in this group'.Code:SELECT "Query" AS ObjectType, MSysObjects.Type, MSysObjects.Flags, MSysObjects.Name, Choose(CInt([flags]/16)+1,"Select","CrossTab","Delete","Update","Append","Make Table","Data Definition","Pass Through","Union") AS SubType, (CInt([flags]/16)-([flags]/16))*2 AS Hidden FROM MSysObjects WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Flags)<>3) AND ((MSysObjects.Name) Like "*_yrly*"));
I'm sorry, I click on the link you provided & opened the file, but I don't understand how to implement into my own database. I probably ought to not be saying this on this website, but I wish I could find someone willing to remotelly access my computer & help me figure this whole thing out. there are onlne consultants who will remotely access your computer & advise you on database designing & etc. However, the heapest one charge $45.00 per hour. Unfortunately I don't have that kind of money I'm not looking for anybody to do my work, but I think the only way I can learn database designing & etc is if I had a personal tutors via, remote cces, but i know that cost big bucks! Please excuse me I'm just ranting here.
@djclinton15 (Dave?)
No problems.
Any/all of the queries could be modified to get the year from a combo box, list box or text box on a form (the form would have to be open when executing the query).Please do not stress yourself if what I send you is too complicated or overwhelming. to dissect, it's not a big deal. Truth be told, with all of the time I spent on this post I could have had manually changed all of my 'yearly' queries over to "2022" from "2021." Yes, there must be a way I can change the year field in my yearly queries, but that is ONLY IF my database is properly designed. If it isn't, then asking for help might seem useless. Right?
Did you see the example dB (Post #20)?
You could have one dB for all of the years and select the year to view. As long as you have the query returning the correct data set, the where clause could get the year from the form.... easy as falling off a log.
Modifying the queries is easy. Here is one
Let's say you have a form named "frmPrintParameters" and a combo box named "cboYear"
if yrs is a text field:
if yrs is a number type field:Code:SELECT Employees_CntOfDys_Yrly_qry.EmployeeID, Employees_CntOfDys_Yrly_qry.CntOfDys FROM Employees_CntOfDys_Yrly_qry WHERE (((Employees_CntOfDys_Yrly_qry.Yrs)= '" & Forms!frmPrintParameters.cboYear &"'));
Code:SELECT Employees_CntOfDys_Yrly_qry.EmployeeID, Employees_CntOfDys_Yrly_qry.CntOfDys FROM Employees_CntOfDys_Yrly_qry WHERE (((Employees_CntOfDys_Yrly_qry.Yrs)= " & Forms!frmPrintParameters.cboYear &"));
But you still need to do back ups!
Yes Steve, my name is Dave; good guess. I much prefer "Dave" over "David," which my mother rather calls me. However, I don't jump out the window whenever anyone calls me, David. Just my opinion, "Dave" always sounds more adult-like than, "David." I have a brother who prefers to be called, Stephen & be always spelled that way. Because of my speech difficulties I just always call him, Steve. I like one syllable first names; my 2-favorate female first names is Jill & Karen. two names you can only say one way. plus, I had a sister named, Karen.
Okay, enough chit-chat & onto some business. in reference to post #32 I copied & pasted Ajax's code into a SQL & I like the results I see in return. Actually, I'm referring back to Colin-? (please excuse me if that isn't your name, Isldogs). I want to know how I can tweak this code. 1) I purposely hide a few of my yrly queries & when I went back to Isldogs query, I refresh the query, but it's still is showing all zeros. I don't mean to complicate things & don't remember if I mentioned this before. not all of my 'yrly' (yearly, I hope you know tat by now :-) queries have the year which needs to be changed. So, as I started going through my yrly queries & the queries that had "2021" I changed to "2022." Sorry guys, it's not you, I just don't have any faith in myself I trying to figure out how to do it any other way. getting back to tweaking Isldog's Post #23 query, for one it's not showing any of my hidden files. Two, say I wanted to see all of my yearly forms or both all of my yearly queries & forms? For the Object Type, in Query Design I tried replacing [ObjectType: "Query"] with [ObjectType: "Forms"] but as you know that didn't fly. I tried playing around with changing the Type number & the Flag number, but I didn't get any results. I need to figure out what the Type Number & the Flag number means. Isldog, I'm not looking for brownie points here but I have been checking out your website!
Steve, back to you, before I tried out your 2-codes in Post # I did first backed up my dbs. I tried your posted codes, except I'm not sure what's suppose to happen?
Guys, I'm just so-so tired & need to call it a night.
Dave,
My Post #34, the RED is a reference to a control on a form where you could enter a year (or select a year using a combo box) .
A working example is in my Post #20 named "Music_Library_11_2015-28-01_Mod_ss.zip". All you have to do is use the combo box to select a year to see the data change.
PS My mom called me Stephen wherever I was in trouble. Luckily, I wasn't in trouble too much when I was young...
The Type and Flags values are just a way of uniquely identifying each type of object in Access databases. AFAIK. they have no intrinsic meaning other than as an identifier.
You said my code in post #23 didn't show your hidden 'files'. What do you mean here? It should show ALL queries but no other database objects.
If you want to list other objects, modify the SQL using the values in the table I provided ... OR import the items from my example database into your own.
BTW I'm currently working on revamping my isladogs website. If you find any issues, please send me a PM if you have time. I'm aware the feedback forms don't currently work but they will be fixed soon