Sharing SQL Queries

Database No Responses »
Nov 102009

 

Today I wanted to post a tip on how to share queries.  Often users or the PDOTeam will post a query and we want you to know how to use that in your own project (like the query below).

 

SELECT [Components-Valves].ID_COUNT_, [Components-Valves].DWG_NAME_, [Components-Valves].SIZE_, [Components-Valves].SPEC_, [Components-Valves].CW_RES_01_, [Components-Process Lines].TAG_, [Components-Process Lines].SIZE_ AS [Line Size], IIf([Components-Valves]![SIZE_]=[Line Size],"Y","N") AS [Match]
FROM [Components-Valves] INNER JOIN [Components-Process Lines] ON [Components-Valves].LINE_ID_1_ = [Components-Process Lines].ID_COUNT_;

 

The query above will display a list of the valves and whether or not their size matches the size of the line they are on.  You can quickly see the drawings that have lines you need to fix. 

Below follows the technique for adding the query to your project database.

First copy the text to the clipboard.

clip_image002

Next, in Access start a new query in Design View from the Create Tab.

clip_image004

Close the Show Table dialog, right-click on the header and choose SQL View.

clip_image006

clip_image008

Paste the query.

clip_image010

Now, you can use the query by saving it and switching to Datasheet view.

clip_image012

Don’t forget that Exporting to Excel to produce or sort a query is really easy too.  Just right-click the query from the Navigation pane, highlight export and choose Excel.

clip_image014

I hope you find this useful!

Posted by Dave Wolfe at 2:40 pm Tagged with: , , , , ,

Updating The Switchboard Interface With CADWorx P&ID

CADWorx, Database, P&ID No Responses »
Mar 042009

 

If you are running CADWorx P&ID, then chances are you have added or removed fields from the database.  This is a great way to really get the information that we need into the drawing, into the database, and more importantly, into the hands of the people that really need the info.

Let’s setup a scenario.  You’ve added a few Columns to the valve table, and you’ve updated the database.tbl file to make sure that the columns you’ve added show up in the additional data section of the Component Edit Dialog Box.

The only problem that we have from this point, is when using the ICES database interface, those fields don’t show up just because we’ve added them into the database and into P&ID.

Let me stop here, I’ve made an assumption that you know about adding database columns into the project AFTER you’ve created the project, if this is NOT the case PLEASE let me know.  Shoot me an email, or message me somehow and I will be GLAD to help you out with that as well.

Now, back to the database, there are a couple of things we need to do to get our information into the ICES switchboard, none of which are difficult. 

First, we need to find the querry that gathers the information into the switchboard, you can see here where we’ve added in the column VENDOR_ for the Valve Table

image

And Here is the Query we need to take a look at:

image

We’ll open this in Design View and add in a query.  Out of the box, it will look like this:

image

Now that it’s in the query, we have one more step, and that is tweaking the header of the query to show up in the dialog box in the ICES Switchboard.  We’ll modify the first line to look like this:

image

This in turn, will give us the following when we take a look at the switchboard:

image

Notice the Vendor tag at the bottom, ready for you, or a member of your team to populate the data.

Not hard, but another situation where we need to know the right steps to get accomplished what we need.

Again, if you have any questions PLEASE feel free to contact me.

KP

Posted by Kyle Pope at 4:51 pm Tagged with: ,

Using a Database in COADE CADWorx Plant

CADWorx, Plant 2 Responses »
Jan 222009

I wanted to talk today about another underutilized tool in CADWorx Plant (and Pro). The Database. Now, some of us have had run-ins with other software that made the database a less than pleasant experience. This doesn’t have to be one of those times. I’m going to talk about a few places where I’ve used the database, and ask you guys to do let us know how you use it.

Database for a spec

OK, don’t get excited, really! This call came in yesterday, a client has a model that was given to them by a contract designer. He used his specs, and now those specs aren’t available to the end user. The model they have looks great, but as we all know, it’s going to change. These guys are left with the unenviable task of building specs that they don’t have, to match a model that they do. To do this, we’ll use a database. Now this isn’t the 1 button "save all" button to fix our problems, but by doing a 1 time data "dump" to an access database, we can get a headstart on the process. To do the one time dump, we’ll go to Plant>Accessory>Database>Export. Select the database type, and name it. This will give you the information you need to build a spec based off what is already in the model. The information exported to a database not only includes short and long descriptions, it also shows the datafile used to create the component. This is HUGE to determine if it was an out of the box file, or one that was created.

Database to Rebuild a model

This one looks really cool, and has some big implications. Once you export to a database, or the case of plant pro, use a live database, you can import that into a new drawing to rebuild all of the piping. Imagine a drawing corruption, if you have a database of the model, you can pull the pipe into a new clean drawing.

Database to change Descriptions

This one has happened to us all. We "finish" up a model, and start turning in drawings and someone doesn’t like the descriptions that we used. An easy way to clean up an entire model in a hurry is with a live database. In the database, use the filter tools in access to align up the descriptions, then use find/replace to bulk change descriptions. Save and close the database, then sync your drawing back up to it.

These are just some of the things we’ve toyed around with. We’ve also done semi-realtime material drags from the database to see where things stand during various stages of the project. With the amount of data that is available, the possibilities are HUGE!

Let me know how you are using database, and as always, if you have any questions, please let me know!

KP

Posted by Kyle Pope at 4:35 pm Tagged with: ,
© 2010 Process Design, from the OutsideSuffusion WordPress theme by Sayontan Sinha