Any way to copy & paste a long list of objects onto another type of document

[Micron]

Not sure. Works for me, but you didn't post the whole thing so maybe there's an issue with the beginning part? I created qryYrly and this works

SELECT *  FROM msysObjects
WHERE [Type] In (5) AND [Flags] In (0,16) AND [Name] Like '*Yrly*'
ORDER BY [Type], [Name]

I get what you're saying about your db. I guess about all you can do is soldier on with what you've got and maybe try to step up your game with the next one. I think that if you were to start over (but keep using what you have) and address one concept or portion at a time and ask for a review before taking the next step, you would be able to slowly convert to something more efficient. If that takes a year, so what, as long as you've got something that's working in the meantime?



Wish everyone was as appreciative as you.

[ssanfu]

It sounds like you are getting closer to what you want as far as getting the names of the queries.

So I'll ask one more time, then drop it. Would you post the SQL of a couple of yearly queries of may even post the FE (the dB is split, isn't it?)?

You shouldn't have to make copies of the queries every year. The year should be a parameter - enter the year in a text box on a form - one query that can pick the records for any year.

[CJ_London]

you need to use the query exactly as I provided in post #10. Although Colin has provided an alternative to Flags (<>3 as opposed to IN (0,16) which is better. If you want to know whether it is a table, query, whatever then include the type in your select statement or limit to one of the type numbers as you have done

Question though, it's listing 155 queries/Recorrds, but that seems too many queries for this database

it may be you have hidden some. To see hidden objects go to File>Options>Current Database, then click the Navigation Options button and tick the Show Hidden Objects option

[djclinton15]

Can I suggest a further refinement to your SQL to list all the queries but exclude all 'temp' queries used in form combos and listboxes.
So you want all objects with Type=5 and Flags<>3

SELECT MSysObjects.NameFROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Flags)<>3));

You may be able to filter the Name field further to just get those for the current year if that is part of the query name

I tried using your code but I got a syntax error. I'm sorry but very confused. Was I supposed to add/include your code in with Ajax code or ...?

[ssanfu]

Ok, I made an example dB on FILTERING the years instead of having to copy the queries and modify the year. This is based on a previous dB that you posted.


I'll step away now....

Good luck with your project.....

[CJ_London]

I tried using your code but I got a syntax error.

you need a space between name and FROM

[isladogs]

Using Flags 0 & 16 will only list SELECT and CROSSTAB queries.
It won't list other query types such as APPEND, UPDATE, DELETE and MAKE TABLE

Did you try my suggestion in post #13? It will list ALL saved queries. You can then filter that further by name as above

[isladogs]

I tried using your code but I got a syntax error. I'm sorry but very confused. Was I supposed to add/include your code in with Ajax code or ...?

Apologies. There should have been a space before FROM as @ajax has mentioned - I've now corrected post #13
Flags values 0 & 16 only covers visible SELECT & CROSSTAB queries.
Using those will omit all other types such as APPEND, UPDATE, DELETE & MAKE TABLE

BTW Hidden queries add 8 to each Flags value

Here is a hopefully complete list of query types and Flags values:
In the final column visible = 0, hidden = -1

ID	Object	Type	SubType	Flags	Hidden
15	Query	5	Append	72	-1
16	Query	5	Append	64	0
17	Query	5	Crosstab	24	-1
18	Query	5	Crosstab	16	0
19	Query	5	Data Definition	96	0
20	Query	5	Data Definition	104	-1
21	Query	5	Delete	40	-1
22	Query	5	Delete	32	0
23	Query	5	Make Table	80	0
24	Query	5	Make Table	88	-1
25	Query	5	Pass Through	112	0
26	Query	5	Pass Through	120	-1
27	Query	5	Select	8	-1
28	Query	5	Select	0	0
29	Query	5	Temp	3	-1
30	Query	5	Union	128	0
31	Query	5	Union	136	-1
32	Query	5	Update	56	-1
33	Query	5	Update	48	0

[CJ_London]

F

lags values 0 & 16 only covers visible SELECT & CROSSTAB queries

Yeah! I realised that when you posted your amendment - The db I used to test the code before posting only had select and crosstabs!

[djclinton15]

Would you please post the Code again that shows me the results representing the illustration above? Thanks.

[djclinton15]

H Steve! You are certainly one of the members here who greatly helped me in the past amidst others likeMicron, June7, pdbaldy (or something like that), orange, just amongst a few members who stand out to me. Anyway, I didn't mean to ignore your request to post a few of my yearly queries SQL, which I will do now. 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?

Here are a few SQL'S I mentioned:

TRANSFORM Sum(Employees_Yrs_SUMS_qry.SumOfDys) AS SumOfSumOfDys
SELECT Employees_Active_CountOfYrs_sbqry1af.CountOfYrs AS [#Yrs], Employees_Yrs_SUMS_qry.EmployeeID, Sum(Employees_Yrs_SUMS_qry.SumOfDys) AS [Total Of SumOfDys]
FROM Employees_Yrs_SUMS_qry INNER JOIN Employees_Active_CountOfYrs_sbqry1af ON Employees_Yrs_SUMS_qry.EmployeeID = Employees_Active_CountOfYrs_sbqry1af.EmployeeID
WHERE (((Employees_Yrs_SUMS_qry.Terminated)=No) AND ((Employees_Yrs_SUMS_qry.Yrs)="2021"))
GROUP BY Employees_Yrs_SUMS_qry.Terminated, Employees_Active_CountOfYrs_sbqry1af.CountOfYrs, Employees_Yrs_SUMS_qry.EmployeeID, Employees_Yrs_SUMS_qry.Yrs
ORDER BY Employees_Yrs_SUMS_qry.EmployeeID
PIVOT Employees_Yrs_SUMS_qry.Mnth;


---------------------------------------------------------------


SELECT Employees_CntOfDys_Yrly_qry.EmployeeID, Employees_CntOfDys_Yrly_qry.CntOfDys
FROM Employees_CntOfDys_Yrly_qry
WHERE (((Employees_CntOfDys_Yrly_qry.Yrs)="2021"));


------------------------------------------------------------------


SELECT Employees_Yrs_SUMS_Yrs_sbqry1.Mnth AS Mnths, Employees_Mnths_SUMS_Yrly_sbqryv.CountOfEmployeeID , [Employees_Yrly_SUMS_Yrly_sbqry1 _b].SumOfDys, [Employees_Yrly_SUMS_Yrly_sbqry1 _b].SumOfHrs, [Employees_Yrly_SUMS_Yrly_sbqry1 _b].SumOfErngs AS SumOfErng
FROM ((Employees_Yrs_SUMS_Yrs_sbqry1 INNER JOIN Mnths_Num_tbl ON Employees_Yrs_SUMS_Yrs_sbqry1.Mnth = Mnths_Num_tbl.Mnths) INNER JOIN Employees_Mnths_SUMS_Yrly_sbqryv ON Employees_Yrs_SUMS_Yrs_sbqry1.Mnth = Employees_Mnths_SUMS_Yrly_sbqryv.Mnth) INNER JOIN [Employees_Yrly_SUMS_Yrly_sbqry1 _b] ON Employees_Yrs_SUMS_Yrs_sbqry1.Mnth = [Employees_Yrly_SUMS_Yrly_sbqry1 _b].Mnth
GROUP BY Employees_Yrs_SUMS_Yrs_sbqry1.Mnth, Employees_Mnths_SUMS_Yrly_sbqryv.CountOfEmployeeID , [Employees_Yrly_SUMS_Yrly_sbqry1 _b].SumOfDys, [Employees_Yrly_SUMS_Yrly_sbqry1 _b].SumOfHrs, [Employees_Yrly_SUMS_Yrly_sbqry1 _b].SumOfErngs, Employees_Yrs_SUMS_Yrs_sbqry1.Yrs, Mnths_Num_tbl.Mnths_NumID
HAVING (((Employees_Yrs_SUMS_Yrs_sbqry1.Yrs)="2021"))
ORDER BY Mnths_Num_tbl.Mnths_NumID;


------------------------------------------------------------------


SELECT Employees_Payroll_Yrs_qry1.EmplyPyrllID, Employees_Payroll_Yrs_qry1.Mnth, Employees_Payroll_Yrs_qry1.WkDy, Employees_Payroll_Yrs_qry1.EmplDate, Employees_Info_tbl.Co, Employees_Ages_qry.Age AS Age, Employees_Payroll_Yrs_qry1.EmployeeID, Employees_Payroll_Yrs_qry1.Hrs, Employees_Payroll_Yrs_qry1.DyEvn, Employees_Payroll_Yrs_qry1.DNW
FROM (Employees_Payroll_Yrs_qry1 LEFT JOIN Employees_Info_tbl ON Employees_Payroll_Yrs_qry1.EmployeeID = Employees_Info_tbl.EmployeeID) INNER JOIN Employees_Ages_qry ON Employees_Payroll_Yrs_qry1.EmployeeID = Employees_Ages_qry.EmployeeID
WHERE (((Employees_Payroll_Yrs_qry1.EmplDate) Between Date()-Weekday(Date())-5 And Date()+(1-Weekday(Date()))) AND ((Employees_Payroll_Yrs_qry1.Yrs)="2021"))
ORDER BY Employees_Payroll_Yrs_qry1.EmplDate, Employees_Payroll_Yrs_qry1.EmplyPyrllID;


------------------------------------------------------------------


SELECT Employees_Payroll_Yrs_qry1.EmplyPyrllID, Employees_Payroll_Yrs_qry1.Mnth, Employees_Payroll_Yrs_qry1.WkDy, Employees_Payroll_Yrs_qry1.EmplDate, Employees_Info_tbl.Co, Employees_Ages_qry.Age AS Age, Employees_Payroll_Yrs_qry1.EmployeeID, Employees_Payroll_Yrs_qry1.Hrs, Employees_Payroll_Yrs_qry1.DyEvn, Employees_Payroll_Yrs_qry1.DNW
FROM (Employees_Payroll_Yrs_qry1 LEFT JOIN Employees_Info_tbl ON Employees_Payroll_Yrs_qry1.EmployeeID = Employees_Info_tbl.EmployeeID) INNER JOIN Employees_Ages_qry ON Employees_Payroll_Yrs_qry1.EmployeeID = Employees_Ages_qry.EmployeeID
WHERE (((Employees_Payroll_Yrs_qry1.Yrs)="2021"))
ORDER BY Employees_Payroll_Yrs_qry1.EmplDate, Employees_Payroll_Yrs_qry1.EmplyPyrllID;

[Micron]

Year value is a string? I'd say that further complicates things a bit. You might try replacing "2021" with something like
(Employees_Payroll_Yrs_qry1.Yrs)='" & Cstr(Date) & "'") or Cstr(Year) - depending on the requirement.

That assumes you'll always want the current year. If not, a form control can decide what year to use and the query can use that reference instead. I posed this question earlier but if there was an acknowledgement of that, I missed it.

Really though, if you have mobility issues you own it to yourself to fix the crux of the problem - eliminate the need to generate a list and reduce these queries to as few as possible by using parameters. You might be able to reduce even further by adding parameters such as whether or not to count, sum, DCount or whatever.

Given your situation and in the spirit of Christmas, I will volunteer some time to assist at my own pace, and if anyone wants to collaborate with me, we can divide this into chunks after deciding on the best approach. However, this would require you to provide a db copy (doesn't have to be posted here). If necessary, I would sign a NDA if it isn't too onerous.

[isladogs]

Would you please post the Code again that shows me the results representing the illustration above? Thanks.

If you are referring to the 'code' (table) I showed in post #23, that is from a reference table I created as part of this example app https://www.isladogs.co.uk/view-data...594424205.html

[Welshgasman]

I think we could just as easy make the query year a number? in that source query?
Could we then not replace "2021" for something like a tempvar? in all the qdf.sql ?

[djclinton15]

Actually, I was referring to Post #23