Access Queries

Access queries help and tips

USING SQL VIEWS TO ADD/EDIT DATA IN ACCESS

This is part II of a two part series on views I've written, you can see part I here. Over the last couple of years I have had a great deal of experience with working on Access Databases with the Data is linked to tables in stored in SQL Server. With relational databases and lookup tables you can eas [...]

USING SQL VIEWS TO ADD/EDIT DATA IN ACCESS2018-09-25T06:14:44-05:00

Find all queries that use a particular table

We just completed a massive 39 database conversion to SQL Server for a client in Tennessee which was quite challenging, we had to not only upsize all the tables, but switch the system from importing/exporting between databases to using linked tables instead. At times we needed to find which queries [...]

Find all queries that use a particular table2016-06-10T22:01:17-05:00

Reporting more granularly than usual

Typically, when we do reporting, we usually do it at a higher granularity. For example, clients commonly wants a monthly report of sales. The database would store the individual sales as a single record, so it's no problem summing up the figures to the month each. Ditto with year, or even going from [...]

Reporting more granularly than usual2016-08-25T22:17:07-05:00

Access User Groups

The purpose of this group is to create virtual chapters throughout the world, from beginning user groups to advanced topics such as SQL Server. Chapters can be organized around a language, such as Spanish, a region, such as Spain or a topic of interest, such as Access functions. We hold monthly meet [...]

Access User Groups2014-07-14T22:52:49-05:00

Whether to exists or not exists, that is the question…

This is second part of 2-part series on SQL clauses. Juan covered the IN() in the first part. We now turn to a close sibling, EXISTS clause. As Juan demonstrated, IN() can be useful for some situations where we want to match a subset of another table without necessarily changing the output due to jo [...]

Whether to exists or not exists, that is the question…2018-02-19T18:36:01-05:00

Are you IN or are you out? (Not IN)

This is a part one of two-part on how you can make your queries much more powerful. In first part, we look at whether we're in or not. Part two can be found here. The IN clause is a great tool to have in your arsenal, it can allow your forms to be editable or filter forms using . What is the IN clau [...]

Are you IN or are you out? (Not IN)2018-02-19T18:35:12-05:00

Matching records using a date range

The other day I asked Ben to help me with a thorny problem, I needed to match records in one table with records in another table using a date range. Here's the table I needed to update, called tblWidgets: [crayon-5d60ecdba5fb7571725785/] WidgetWeekID comes from another table called tblWidgetWeeks: [ [...]

Matching records using a date range2011-10-10T20:43:05-05:00

Code to help you debug SQL code – POPQuery

I use SQL in my code a lot, I'm not a fan of creating queries and then referencing them in my code  since users may delete or change them. Often I build SQL strings and then I need to debug them in the query Access grid, in the past I would get the value of my SQL string in the immediate window and [...]

Code to help you debug SQL code – POPQuery2010-10-22T16:03:51-05:00

Criteria for the current month in a query

The other day I needed to create a query where it returns all records with dates for the current month. I did not want to hard code the beginning and ending dates in the query, so I came up with the following line of code you can paste into your query: Between CDate(Month(Date()) & "/1/" &am [...]

Criteria for the current month in a query2009-11-06T10:55:52-05:00