UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Too Many Queries, Access 2010    
 
   
firlandsfarm
post Mar 20 2017, 02:09 AM
Post#1



Posts: 246
Joined: 28-April 02
From: Heathfield, England


Hi, I would just like some ideas on how other people deal with too many Objects (queries, tables etc). As I am sole user developer (I use the latter term very loosely smile.gif ) of my databases I tend to work in queries and tables with the odd bit of macro automation. Maybe that means I am missing a trick by not using forms (which would require a small investment of learning time), SQL (a medium investment of learning time) and VBA/modules (a large investment of learning time), but I just feel happy bespoking something together quickly using queries.

I'm a Folders kind of guy in preference to being a scroller. I like to see a logical path to finding my query than scroll through a long list of query names mumbling "now what did I name the thing". My problem seems to be many of my queries need pre-queries and temporary tables to prepare data for the final process. I should add I don't do this by choice, just when Access insists that I do.

I could solve this if I could add sub-categories to the custom list of categories in the left margin but cannot see how that is done. I could just dump all queries into a holding category and run everything from the 'final' query but then the 'dump' would replicate the problem when I try to create another extract and think "wait, I already have a query that does that". I would prefer the folder route because I would then store processes similar to an HTML page with the main items in the first folder and the supporting queries/tables in a subfolder keeping everything neatly together.

As I said, I'm just interested in what others do to keep all their objects tidy rather than re-invent the wheel.
This post has been edited by firlandsfarm: Mar 20 2017, 02:12 AM

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
RAZMaddaz
post Mar 20 2017, 08:02 AM
Post#2


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


Hi firlandsfarm, good morning/afternoon!

Suggestion.....

When I have a project that have a lot of Queries, I separate the Queries into related groups. Then I create a Form and add X amount of buttons to run the Queries. Next to each of the buttons I add the name of the Query and some notes of what the Query does and what the Query is used for.

The most important thing to do, in order for this to work is the "Front End", "Back End." The "Back End" is where all your Tables are located. And the "Front End" is where all your Queries, Reports and Forms are located. Then you MUST link the "Front End" to the "Back End" in order for all this to work!!!!


RAZMaddaz
Go to the top of the page
 
firlandsfarm
post Mar 20 2017, 08:47 AM
Post#3



Posts: 246
Joined: 28-April 02
From: Heathfield, England


OK, that sounds a good idea RAZ, I'll look into that a bit deeper ... somewhere in the back of my mind it's saying 'dashboards' ... I used a dashboard decades ago (I think it was V2!) if I remember rightly I had a screen with lots of buttons that caused queries to run ... is that worth looking into because the database was definitely not split then. I'm thinking maybe a dashboard for each area/category where I can list the pre-queries and what they do. Thanks RAZ I'm off to explore smile.gif

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
RAZMaddaz
post Mar 20 2017, 08:52 AM
Post#4


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


yw.gif

RAZMaddaz
Go to the top of the page
 
bluedanube
post Mar 20 2017, 11:44 AM
Post#5



Posts: 282
Joined: 15-July 13



Let me donate a little toy, that you can import.

It will display a list of queries, that have a certain keyword in their SQL statement, which may be a field name or another query name.
If you click on a result item, the query text will be displayed.
If is is a select query, you can open it directly from the list, with an [open] button.

The purpose is, you can find all your queries that rely on a stacked 'sub' query, e.g. when you want to change the name of that.
Thus you can easily rename your queries to improve the readable structure of your query library.
You can also search for queries, that return a certain column, and then open one of these.

Open the form, and enter a search string.
(Initially, there will always be a #NAME error because I didn't care to prepare an empty list.)
This post has been edited by bluedanube: Mar 20 2017, 11:47 AM
Attached File(s)
Attached File  QuerySeeker.zip ( 32.62K )Number of downloads: 7
 
Go to the top of the page
 
firlandsfarm
post Mar 21 2017, 02:45 AM
Post#6



Posts: 246
Joined: 28-April 02
From: Heathfield, England


Hi Blue, that little snippet works great ... thanks. I think I am going to take the more disciplined approach of structuring my Objects by grouping in categories with a dashboards style form for access to them (I'm prefixing the query names with "Dash1...", "Dash2..." etc to name-link them with the appropriate Dashboard). It seems to be working so far. As for your little snippet I am already putting it to good use by searching for all queries that are related to help me to group them. smile.gif

RAZ, your idea is working great but I'm intrigued why you suggested I will need to split my database to Front End and Back End ... everything is working fine without splitting. Is there an advantage in splitting for this purpose that I'm not seeing?

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
RAZMaddaz
post Mar 21 2017, 08:03 AM
Post#7


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


Hi firlandsfarm.

QUOTE
RAZ, your idea is working great but I'm intrigued why you suggested I will need to split my database to Front End and Back End ... everything is working fine without splitting. Is there an advantage in splitting for this purpose that I'm not seeing?


I have a project in which I need to create dozens of charts and with those charts, there are dozens of Queries. So, what I like to do is create a Front End (FE) and Back End (BE), and have the related Queries and Reports that are used to create the Charts in each Database/File/Front End. This way, when I need to make changes to the Queries, there are less Queries in the Database and easier to find the Query I need to change. I then have a Form with a few buttons to run the Queries and the little notes about each of the Queries on the Form.

Now you could do it this way, or maybe you might want to create a Form with a Tab Control and then have the related Queries in each Tab.

RAZMaddaz
Go to the top of the page
 
bluedanube
post Mar 21 2017, 09:21 AM
Post#8



Posts: 282
Joined: 15-July 13



>>everything is working fine without splitting

it will depend on the life-cycle and origin of the data.
if you have some kind of through-put with new data from different origins, then it would be quicker to keep the frontend as it is, or also to develop it further, while data comes and goes independently in it's own pace.
Go to the top of the page
 
firlandsfarm
post Mar 21 2017, 10:40 AM
Post#9



Posts: 246
Joined: 28-April 02
From: Heathfield, England


>> ... maybe you might want to create a Form with a Tab Control and then have the related Queries in each Tab

RAZ, having just reminded me about macro buttons and dashboard forms you now introduce Tab Control and queries related to them ... what's that? KISS ... buttons work so I'll use buttons. smile.gif

>> it will depend on the life-cycle and origin of the data.

Blue, that sounds a bit like a mix of a David Attenborough and Darwin for databases! smile.gif The origin of the data is a mix of front-end and back-end in a way ... for this project I use Access to interrogate a proprietary MS SQL database on my computer and create/populate own tables within Access from external data sources to compliment the MS SQL data. The extra data is often imported Excel/CSV worksheets. I never update nor add to the MS SQL database for fear of damaging it! I'm thinking of using pass-throughs where a query is totally related to the MS SQL data to see if that speeds things up.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
bluedanube
post Mar 21 2017, 01:49 PM
Post#10



Posts: 282
Joined: 15-July 13



So the philosophic question is answered, in that some SQL-Server and some spredsheet and listing data take the role of the back-end.
I conclude, that you need not split.
smile.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th November 2017 - 01:54 AM