CADWorx & Access Database queries

Let me start off with a disclaimer.  Learning queries will make your head hurt.  It takes a lot of experimenting to make sure you are getting everything you want.  This post will cover the basic structure of queries (also known as views) and some tips to learning to work with them.  I'll be using Access 2007 for these excercises.

On a side note, clicking on the pictures will expand them to a full size image.

Files: Db Example on CW Forums

For this tutorial, we'll do a simple query and add some twists at the end.  Our goal will be to find a line number in Plant and then add some pieces together to find information most of us would use in reports (model file, long desc.) .

1. Open the CheckForExisting Access DB, go to the create tab, and click Query Design.
2. From the "Show Table" dialog, select the PIPE table, click Add->Close.

For this exercise, lets add the model the pipe is in (DWG_NAME), the line number of the pipe (LINE_NUM), and the long description (LONG_DESC).  Do this by double clicking each of the fields from the pipe table.

 

Viewing the query as a datasheet will allow us to see the results (right click on the titlebar).

Obviously, this is a general report that can be used as a BOM of sorts.  But what if we are looking for a specific line?  At this point, the criteria field comes into play.  Underneath the LINE_NUM coluumn, right-click in the criteria field and select Build.

 

This brings up the seemingly innocent, but very powerful Expression Builder.  In it, enter Like '*1460*' and click ok.  Going to a datasheet view with this criteria will show these results:

 

Behold, all the lines that have 1460 in them!  Let examine the expression we used in this criteria.  The word "Like" tells the db to include close (not exact matches) when used in conjunction with wildcard characters [astericks(*)].  The 2 astericks tells the databse to allow any number of letters or number before or after the number 1460.  I use single quotes (') here as a matter of good practice.  If we would have done (Like '3"-ME-1460*') we'd need the single quotes to allow for the inch mark in the line number. 

Obviously, there are many more words in the SQL language which I can't cover in this post.  We'll do one more for practice, the "Not".  From the previous example, we can enter Not Like '*1460*' to display the lines that don't have 1460 in them.

The final example we'll use is incorporating a function from VBA into a query to show (or not) the existing components in the project.

On the Access pane, make sure Object types is select, and then select Queries.

 

Double-click to open the Flag Filter Query.  You should see the datasheet view which looks like this:

Open it in Design View by Right-clicking in the titlebar.  In the 5th column from the left (Status), you'll see something quircky.  Right-click in the Status: Field and select build. 

In the Expression Builder, you'll see all the database objects available. You can use other queries/tables in your columns to get the results you need.  As I said, for now, we'll just demonstrate using a custom VBA function.  Find the Function folder and double click on it.

 

All the VBA modules associated with this database are located here.  Double click the CheckForExisting folder, and you'll se the FlagFind, and FilterByFlag items display.

 

Double clicking one of these adds the function to the build.  The "<<" and ">>" say that there is a value that needs to be input here.  From the values in the Status: field already, the <<flag>> should be replaced with the Table and column ([PIPE]![FLAG]) that contain the flag field, and the <<FlagSearchValue>> ("4") is the value of the flag that you want to search for.

With the Expression builder looking like this:   we have the first half of what we need to filter based on existing components.  The second half is adding "Has 4" (to only display existing components) or Not "Has 4" (to only display components that are not existing) to the criteria line for the Status field.

Going to DataSheet view, we get these results.

 

Hopefully, that gets you where you need to go for the basics.  I've included in the SampleDBs.zip, a Line List Template.mdb which shows how to get a bit nicer line list out of P&ID, using some of the basics discussed here.  Please note, that it doesn't work well for lines running through multiple P&IDs, and the blanks show entries in the Link Database that have errors.

SQL: The Language

SQL-Access queries are based on a language called SQL (sEE-kwal).  As with most languages, there are several dialects and the SQL in Access is one of them.  Because of that some of what is said here doesn't apply to SQL Server (different wildcard characters, more functionality), but the basic structure is the same.

Since queries are just SQL, it makes the really easy to share.  Right click on any query (as if going to design view or datasheet view) and select SQL view.  The SQL view shows what is going on under the hood.

Let's examine the query for existing components we just ran.

SELECT PIPE.DWG_NAME, PIPE.TAG, PIPE.LINE_NUM, PIPE.SHORT_DESC, FilterByFlag([PIPE]![FLAG],"4") AS Status
FROM PIPE
WHERE (((FilterByFlag([PIPE]![FLAG],"4"))="Has 4"));

The first part (   ) denotes the columns that should be included in the datasheet view.

Notice the FilterByFlag([PIPE]![FLAG] AS Status section.  SQL allows you to change the way a column name is displayed using "AS".  So, if we didn't want LINE_NUM to display, we could do this: PIPE.LINE_NUM AS [Line Number].  The brackets tell the DB that it should include everything in between them as the column name.

The 2nd part (    ) denotes what table to pull the values from (this can also be another query).

The 3rd part (     ) is the criteria. It includes the column without the criteria is applied to (FilterByFlag([PIPE]![FLAG],"4") and the filter it will apply to that column (where the column value = "Has 4".

In any case, to share queries, all you simply have to do is open a new query in SQL view, and paste it in.

I hope that this introduction to queries will help.  Feel free to reply with questions/ideas.

Comments

[generic cialis]

I'd like to do an online quiz where a picture is displayed and below
it a graphic that says "click to see answer" if you click on that
graphic, the answer is displayed. I'd like to have a page with 15 or
so quiz items, and people can just go one-by-one and click on each
answer button to see the answer.

[Cheap Computesr Canada]

Interesting blog will like to keep tabs.