Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS ACCESS QUERY query

Posted on 2013-11-08
17
195 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

840 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