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
> Brand New To Access - Filtering, Any Version    
 
   
Steevg53
post Jan 14 2019, 05:51 AM
Post#1



Posts: 4
Joined: 13-January 19



Hi everyone, & Happy New Year.

As the topic title says, I'm brand new to Access 2019?) but have been given a rather large database to filter from several tens of thousands of entries to something more manageable. My first inclination was to export the database to excel and use a more familiar method to filter, but after several hours the final results have become corrupted along the way somehow.

I understand it's possible to filter a database to provide the exact results I require, but have no idea where to start.

Would someone be kind enough to either outline the method to create my filters (Queries?), or point me to a URL where I might be able to find guidance.

Many thanks
Go to the top of the page
 
isladogs
post Jan 14 2019, 06:40 AM
Post#2


UtterAccess VIP
Posts: 1,808
Joined: 4-June 18
From: Somerset, UK


Welcome to UA.
I have several examples of grouping and filtering on my website.
For example, have a look at Multiple Group and Filter though it may be more complex than you need
This post has been edited by isladogs: Jan 14 2019, 06:41 AM

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
GroverParkGeorge
post Jan 14 2019, 08:28 AM
Post#3


UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA


One of the most important things to understand is that a "database" contains tables, and that the data is stored in those tables. Therefore, "filtering a database" really means "filtering data in one or more tables".

That's where Colin's reference to queries comes in.

You mention the business purpose here is to filter a large dataset into "something more manageable", but that can mean a whole lot of different things. One of those things, though, is that you want to DISPLAY subsets of that larger dataset, tailored to a specific set of criteria. It DOES NOT mean getting rid of any data, though. I suspect that's one of the unfortunate consequences of putting the data into Excel and manipulating it there.

I liken databases to a sort of store front, say a dry cleaners. In the front, you have a nice shiny counter where customers (users of a database) come to drop off new items (add data) and pick up processed items (reports based on their data). In the back, you have storage bins and carts where the items are stored (tables containing data). And in between, you have machines that clean, dry and process items (code). The way you move items back and forth between the front and the back room is with filtering and sorting using some sort of tagging system because you need to sort the items by customer, by type of material, by process to be used, and so on. That's your queries. You write queries to extract data according the criteria you define. You want to see items belonging to Customer A, you write a query that says: SELECT ITEM FROM TABLE WHERE CUSTOMER ="A" That query brings back everything for that customer and no other customers. It gets a lot more complex, of course, as you add additional tables, and use more refined criteria. However, all querying is based on that same idea.

So, to get the subsets of data you need, first define the criteria that will return that data, and only that data from the table or tables where it is stored.

This analogy ends, I should add, with the realization that in a dry cleaners, items are submitted and removed by customers. In a database, we are usually adding data all the time, but deleting it from tables is a rare event, to be undertaken with considerable caution.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Steevg53
post Jan 14 2019, 04:17 PM
Post#4



Posts: 4
Joined: 13-January 19



Hi Colin, those examples are exactly what I'm looking to achieve. I have approximately 7 criteria that I need to filter the results by, and it looks like I can do this by creating a form to filter (query) the results in the table? Not too sure about the string info though, do I need to compile that myself or is this purely the result of the filtering in place in one of your examples?

George, also thanks for the overview of how the database works, I understand the basic idea and your explanation and comparisons to the dry cleaners is easy to understand. The issue with the excel filtering (as you suspect) is that unlike a database, excel information is stored by cell, and all cells in a row are not locked together, and can therefore be moved if the user is not careful to always select the whole sheet before filtering. given the size of the data I'm working with, I suspect thats happened at some stage during filtering. The results of that excel filtering therefore cannot be regarded as reliable.

I'll take a look at how I might create a form to start with (as per in Colins example), however if either of you have any further suggestions or comments I'd be very grateful for the help.

I'm attempting to put together a smaller example of the database information in the hope that I may be able to share it at some stage for evaluation.

Many thanks.
Go to the top of the page
 
tina t
post Jan 14 2019, 05:10 PM
Post#5



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


PMFJI, but i have one comment FWIW: suggest you forget about forms for the moment. get your query(s) built correctly first, so that you are able to pull the target data from the criteria you set. once you have that figured out, then you can build a form or forms to allow users (including yourself) to change criteria at will to return different datasets from the table(s) involved.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
isladogs
post Jan 14 2019, 05:53 PM
Post#6


UtterAccess VIP
Posts: 1,808
Joined: 4-June 18
From: Somerset, UK


@tina_t
Whilst I totally understand your point, the way my examples are constructed uses concatenated strings based on selected values in several combos.
I'm not using saved queries (though it can be useful to construct a query then convert to a SQL string

Therefore if the OP wants a similar approach, creating the form first (or simultaneously) is perfectly sensible


@steevg53
You would need to create your own concatenated strings
What i would STRONGLY recommend in such cases is building up step by step.
Start with one combo, get the code working.
Thn add another, rinse and repeat until each can be used together successfully

as I said above it can be useful to construct a query then convert to SQL
My SQL to VBA & back again converter app may be useful here - its an updated version of an app by Allen Browne
Allen Browne's website has loads of very useful examples to assist with developing apps of this kind

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
tina t
post Jan 14 2019, 08:13 PM
Post#7



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


@colin,
i respectfully disagree. the op says he's new to Access. rather than use somebody else's complex (to him) solution, i'd recommend that he learn to apply criteria in queries, so he understands what he's doing, first. then, he can move on to implementing a complex user-interface solution when he's better equipped to understand what it's doing and why.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Steevg53
post Jan 15 2019, 02:42 AM
Post#8



Posts: 4
Joined: 13-January 19



Hi Colin (& Tina),

Thanks again for your input to my initial post, but I'm still unsure how to start "concatenating strings" - are there any online tutorials which you might be able to point me towards which could help me get started? I'm happy to learn, but my programming skills are zero.
Go to the top of the page
 
tina t
post Jan 15 2019, 03:59 AM
Post#9



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


QUOTE
I'm happy to learn, but my programming skills are zero.

you're right where many of us started with Access - no background of other programming skills, and no formal education in building computer applications. it's definitely not an insurmountable obstacle, so take heart!

as i stated in my previous posts to this thread, IMO, you would be best served by learning how to apply criteria to data, which is a very basic skill that you will use in many ways as you build an Access application. and, again IMO, a query Design view is a good place to work on that skill, because it is structured to help you comprehend just what you're doing, and get immediate feedback on your efforts by just switching from Design view to Datasheet view. if you haven't already, suggest you consider investing in a good basic Access manual which will - among other things - teach you the basic concepts of querying data. the Access<version> Bible is one such manual; i'm sure there are others, but i mention this one because i have personal experience of taking a basic Access class that used it as the textbook.

building text strings is another skill that will serve you well in many areas in building an app, including filtering records in a form, and creating more complex solutions such as the one you and Colin have been discussing. it's usually not difficult once you understand what you're doing, but takes a bit of work and practice to master. i think it will be easier for you to tackle if you've already gotten a comfortable understanding of applying criteria to data first.

anyway, that's my best advice, hon. Colin has a different POV, and i imagine he will also offer you his best advice. it's up to you how you choose to proceed.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
isladogs
post Jan 15 2019, 04:39 AM
Post#10


UtterAccess VIP
Posts: 1,808
Joined: 4-June 18
From: Somerset, UK


Having read the sentence that Tina also picked up on, I would also hold fire on this until you have mastered the basics.
The Access Bible is excellent but you may want something more basic to start with.

There is a comprehensive set of over 100 free videos available by Steve Bishop available on You Tube covering the full range from beginners through to advanced.
See https://www.youtube.com/playlist?list=PLSHn...dDSHBwz7WOEe5yZ
You might find videos 11 -18 particularly useful.

Then once you feel more confident/knowledgeable, come back to this task

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Steevg53
post Jan 15 2019, 10:37 PM
Post#11



Posts: 4
Joined: 13-January 19



Tina/Colin (ladies first . . . ),

Many thanks again for the help and advice - I'll lookup the Access Bible and also check the videos you sent the link to Colin. I'll get through the basics and then come back with anything further.

Appreciate your prompt attention to my questions and look forward to being in touch again soon (I expect).

Thanks
Go to the top of the page
 
tina t
post Jan 16 2019, 02:26 PM
Post#12



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


we're always happy to help, and somebody always has the light on, hon. good luck with your project! :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2019 - 01:55 AM