Database SELECT function

[Snickers]

[code]
Let>ACCDB=select CUSTOMER_NBR from JOBS where CUSTOMER_NBR='105032'

DBQuery>dbH,ACCDB,CUSTOMERS,numrecs,numfields
[/code]

Is it possible to write a code that works like below?

[code]
Let>ACCDB=select CUSTOMER_NBR from JOBS where CUSTOMER_NBR='105032' OR '101080' OR '105032' OR '116007'

DBQuery>dbH,ACCDB,CUSTOMERS,numrecs,numfields
[/code]

[Marcus Tettmar]

Not sure I know what your question is. Are you looking for help with your SQL?

[Snickers]

Ignore my initial code snippet. I have changed my needs since.

I am trying to gather information from an access 2007 database (*.accdb)

I need to pull certain fields (columns) from certain records (rows) only if a certain criteria is met.

For example:

I have a table called JOBS that has thousands of entries that are both active jobs and historical jobs. This table contains a field named J_DEPT_CODE. I need to a to search this JOBS table and find all the entires in the J_DEPT_CODE that equal 52 or 53 and before getting this information. This will produce 2000+ entries. I then need to narrow this search down to specific customer numbers. The customer number of each record (row) is stored in the field CUSTOMER_NBR. This customer number must be any one of the following numbers (these numbers are fictional for simplistics sake): 11111, 22222, 33333, 44444, 55555. Each customer of ours has multiple locations throughout the country, therefore we identify our customers by their shipping number/CUSTOMER_NBR.

Once i have identified the records that are in our department (52,53) I need to make sure it matches at least one of our customer's possible locations using the CUSTOMER_NBR. If these two criteria are met, I need to compare the information in that record's J_JOB_NUMBER field with our active job table called, JOB_ACT.

The table called JOB_ACT contains just one field--J_JOB_NUMBER--containing the current active job numbers.


Search JOBS table for entries that have (J_DEPT_CODE of 52 AND 53) AND (CUSTOMER_NBR of 11111 OR 22222 OR 33333 OR 44444 OR 55555) and whose J_JOB_NUMBER match the J_JOB_NUMBER of table JOB_ACT.

I know this is a big request for assistance and I thank you for your help. You guys have always been helpful.

I appreciate it.

[Marcus Tettmar]

I'm still not sure I know what your question is. Are you asking what function(s) you should use or are you asking someone to develop it all for you? If the former, you are right to use DBQuery. You already know the table structure. If not, ask your DB Admin. The following article should help:

http://www.mjtnet.com/blog/2008/04/15/u ... functions/

[Snickers]

[code]
Search JOBS table for entries that have (J_DEPT_CODE of 52 AND 53) AND (CUSTOMER_NBR of 11111 OR 22222 OR 33333 OR 44444 OR 55555) and whose J_JOB_NUMBER match the J_JOB_NUMBER of table JOB_ACT.
[/code]


Is it possible to do a query using one query select string in macro scheduler that will pull all records whose field called J_DEPT_CODE contain a 52 or 53 value.

Maybe I worded my first question incorrectly?

Just to make sure we are on the same page, the word 'field' refers to a column of data; the word 'record' refers to a row of data.

[code]
Let>ACCDB=select * from JOBS where J_DEPT_CODE='53'
DBQuery>dbH,ACCDB,DEPT,numrecs,numfields
[/code]

The above code will create an array containing all of the fields within all of the records that have a value of 53 in the J_DEPT_CODE.

AT THE VERY LEAST, I need an SINGLE array to be created that contains all of the fields within all of the records that have a value of 52 OR 53 in the J_DEPT_CODE.

if this is not possible, i can create two arrays called *_52 and *_53 if I must. However, a single array based on multiple criteria would be much better and easier to manipulate.

If it is at all possible to create a multi-criterian based array?

I would really like to create a single array containing the following:
[code]
Search JOBS table for entries that have (J_DEPT_CODE of 52 AND 53) AND (CUSTOMER_NBR of 11111 OR 22222 OR 33333 OR 44444 OR 55555)
[/code]
after I have an array created with the above idea in mind, I can simply compare the first column (field) of the JOBS table to the 1st column (field) of the JOB_ACT table.

[code]
whose J_JOB_NUMBER match the J_JOB_NUMBER of table JOB_ACT.
[/code]

My overall question, i suppose is, can the DBQuery search for more than one value at a time.

[code]
AT THE VERY LEAST, I need an SINGLE array to be created that contains all of the fields within all of the records that have a value of 52 OR 53 in the J_DEPT_CODE.
[/code]

this function question is only a tiny portion of what my script is doing.

[Marcus Tettmar]

Yes, your SQL can have more than one criteria, you can say WHERE J_DEPT_CODE=52 OR J_DEPT_CODE=53

DBQuery will produce a single array.

[Snickers]

http://en.wikipedia.org/wiki/Select_(SQL)

It may be asking a bit much, but for us newbies to SQL, it would be great if there was a micro-tutorial on the possible rules for DBQuery such as how many criteria and coding for the select statements.

This is great that it dbquery can do this within MScheduler.

Thanks again.

[Marcus Tettmar]

A tutorial or discussion on the rules of SQL is beyond the scope of Macro Scheduler. It depends entirely on the database you are using. SQL is a language in itself and different database technologies will have different nuances. SQL has been around for many years before Macro Scheduler came along. DBQuery will accept a SQL SELECT statement valid for your database. You really need to consult with your database provider's documentation to find out what you can do. There are already hundreds of books, courses and tutorials available on the subject of SQL. You might want to start with:
http://www.w3schools.com/sql/default.asp

[Snickers]

That is a great web site. It has answered my SQL concerns. Thank you