Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS ACCESS QUERY query

Posted on 2013-11-08
17
Medium Priority
?
201 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
[X]
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
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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 93

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 400 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

721 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