Solved

MS ACCESS QUERY query

Posted on 2013-11-08
17
183 Views
Last Modified: 2013-11-09
I have an Access DB with many records as below. Well, with more meaningful data but you get the idea...

A  10
B  10
C  10
D  12
E  12
F  12
G  13
A  13
I   13
C  13

Is it possible to produce a query that just extracts records with the numbers 10, 12, 13? i.e. on change of that field..? These records would then be used in a report ..

Thanks
0
Comment
Question by:Dave_B_C
17 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39634417
In the criteria section of that field, try this:

Like "10" or "12" or "13"

Flyster
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39634484
Do you have an Autonumber field (ID) that can be used to indicate overall order?

If so, you can try this (lets call the columns you displayed above Letters and Numbers.  Replace with your actual field names as appropriate):

SELECT YourTable.Letters, YourTable.Numbers
FROM YourTable.ID INNER JOIN 
(SELECT MIN(ID), Numbers 
FROM YourTable
GROUP BY Numbers) q
ON q.ID = YourTable.ID

Open in new window


The inner query gets the one row where the number has changed; the outer query lets you pull any of the other needed fields from your table.
0
 

Author Comment

by:Dave_B_C
ID: 39634490
There are 575,000 + records in the DB.. I just showed an example.. In actuality there could be up to 50,000 different values, each of which has 10 digits. It would be a pretty big like statement and a bit tricky to set up? :)
0
 

Author Comment

by:Dave_B_C
ID: 39634502
Can you put that code in a query..? I've only done simple selects before?
0
 

Author Comment

by:Dave_B_C
ID: 39634508
I don't have an autonumber id, the records are grouped by date and time..
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39634666
Ok -- It really helps to have exact field names, structures, etc rather than very simplified examples like this - those can make all the difference in the world.

Can you post a sample database containing your table with just enough records to demonstrate this?  Any sensitive data should be masked or removed.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39635116
Continuing your example a few more rows:


A  10
B  10
C  10
D  12
E  12
F  12
G  13
A  13
I   13
C  13
X  10  <---  I added this row and the ones below it
Y  10
Z  10

Open in new window


Would the 10 show up a second time in your results?  And as Miriam indicated, you need to have some other field, such as a date/time or an autonumber, to establish the order...
0
 

Author Comment

by:Dave_B_C
ID: 39635387
The order is date/time, and it is highly unlikely that 10 would show up again, but if it did it would need to be selected..  It would actually be £10 but I don't think that makes a difference? Ideally I would want to select on the date time field as well..

I will attach a scaled down version of the DB later..

Dave
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Dave_B_C
ID: 39635534
Example DB uploaded...
Expert-Example.mdb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39635622
If you are interested in selecting rows where ANY of the fields have changed, SELECT DISTINCT should work -- Your date field needs to be formatted for this to work right:

SELECT DISTINCT [Expert Example Table].NumberofClients, [Expert Example Table].NumberOfLocations, [Expert Example Table].TotalVolume, Format([Date&Time],'yyyy-mm-dd hh:nn:ss') AS [DateTime]
FROM [Expert Example Table];

Open in new window


If, as your original question describes, you are only interested in changes in the number of clients field, this will work.  It will ONLY work in Access (other platforms don't support the FIRST function), and the FIRST function is not ideal unless you can reliably sort your records.
SELECT First([Expert Example Table].[Date&Time]) AS [FirstOfDate&Time], [Expert Example Table].NumberofClients, First([Expert Example Table].NumberOfLocations) AS FirstOfNumberOfLocations, First([Expert Example Table].TotalVolume) AS FirstOfTotalVolume
FROM [Expert Example Table]
GROUP BY [Expert Example Table].NumberofClients
ORDER BY First([Expert Example Table].[Date&Time])

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39635639
Regarding the SELECT DISTINCT method and formatting the date field... you can format the field according to the intervals you want reported.  What I posted earlier shows changes by the second.  This would have different results:

SELECT DISTINCT [Expert Example Table].NumberofClients, [Expert Example Table].NumberOfLocations, [Expert Example Table].TotalVolume, Format([Date&Time],'yyyy-mm-dd') AS [DateTime]
FROM [Expert Example Table];

Open in new window

0
 

Author Comment

by:Dave_B_C
ID: 39635671
I'm actually just interested in records where the Totalvolume field has changed. I'm not sure how to put these commands into a query? If you could add such a query to the DB and re-upload it that would be great..!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39635697
These responses are very typical of answers you will get to any query questions you ask here, so you should know how to use and test them independently

1. Create a new query, in design view (the exact steps depend on the version of Access)

2. Close the 'Show Table' dialog without adding any tables to the query

3. Right-click the query window and select SQL View

4. Copy/paste the SQL command from the posted comment to the SQL window.  (Access automatically places "SELECT;" in the SQL window.  You should delete/overwrite this when pasting the code into the window)

5. Right click the SQL window and select Datasheet view to see the output (or Design view for the view that you are probably more familiar with)


Post back with any questions about that -
0
 
LVL 61

Accepted Solution

by:
mbizup earned 100 total points
ID: 39635708
For changes in the Total Volume, try this... just copy/paste the whole thing into the query builder (SQL View), as described above:

SELECT First([Expert Example Table].[Date&Time]) AS [FirstOfDate&Time], First([Expert Example Table].NumberofClients) AS FirstOfNUmberOfClients, First([Expert Example Table].NumberOfLocations) AS FirstOfNumberOfLocations, [Expert Example Table].TotalVolume
FROM [Expert Example Table]
GROUP BY [Expert Example Table].TotalVolume
ORDER BY First([Expert Example Table].[Date&Time])

Open in new window

0
 

Author Closing Comment

by:Dave_B_C
ID: 39635753
Very helpful and results perfect
0
 

Author Comment

by:Dave_B_C
ID: 39635754
Have set up and run the query and it is just what I wanted.. Many thanks! :)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39635758
Glad to help out :-)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now