Solved

MS ACCESS QUERY query

Posted on 2013-11-08
17
197 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

710 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