Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

MS ACCESS QUERY query

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
Dave_B_C
Asked:
Dave_B_C
1 Solution
 
FlysterCommented:
In the criteria section of that field, try this:

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

Flyster
0
 
mbizupCommented:
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
 
Dave_B_CAuthor Commented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Dave_B_CAuthor Commented:
Can you put that code in a query..? I've only done simple selects before?
0
 
Dave_B_CAuthor Commented:
I don't have an autonumber id, the records are grouped by date and time..
0
 
mbizupCommented:
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
 
Patrick MatthewsCommented:
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
 
Dave_B_CAuthor Commented:
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
 
Dave_B_CAuthor Commented:
Example DB uploaded...
Expert-Example.mdb
0
 
mbizupCommented:
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
 
mbizupCommented:
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
 
Dave_B_CAuthor Commented:
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
 
mbizupCommented:
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
 
mbizupCommented:
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
 
Dave_B_CAuthor Commented:
Very helpful and results perfect
0
 
Dave_B_CAuthor Commented:
Have set up and run the query and it is just what I wanted.. Many thanks! :)
0
 
mbizupCommented:
Glad to help out :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now