Can Access Query Do This?

[txdave35]

Hey guys,

I was excited to recently discover how to connect Microsoft Access directly to our DB2 mainframe data. This has opened up some new possibilities for our users.

I want to design user friendly queries for our accounting people who are not so tech savvy. I discovered that I can create an Access query that will prompt for information like a SSN and then retrieve that record.

I want to take things further. Like they enter the SSN and the query comes back and tells them if they have payments in the current year or not. In SQL, it would be something like a CASE statement.

Is there any kind of conditional type logic that I can do with Access? Any suggestions would be appreciated. Thanks!

 

[SkipVought]

Hi,

I do something similar for my users of Excel. I have made nearly 100 user defined functions, that can be used on a spreadsheet like any other spreadsheet function, many of which require one or more arguments. These functions open a database, like Oracle or DB2, and execute a query, using the provided arguments as criteria, and return a value. So my users can get data, related to the items on the spreadsheet, like the Nomenclature or Inventory Quantity for a Part, given the Part, or the Order Quantity or Need Date for an Order, given the OrderID.

If you would like to see how I do that with VBA code, please repost your question in Forum707 and I'll post an example there for you.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

By the way, yes it is possbile, in fact I often use MS Access to link tables from multiple databases, so that I can join data and get resultsets from, say, DB2 and Oracle tables, along with Access tables and even Linked Excel workbooks.

I also use MS Query in Excel to get data from DB2, Oracle, Access and other Excel workbooks. This can be an extremely powerful data acquisition tool.

The method I use in the previous post is ActiveX Data Objects, which takes VBA code.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

txdave35,
IMO the only way to get user input is through controls on forms or clicking a button in a MsgBox(). Consider creating a form for users to select criteria. I would then build a SQL statement and use it in a pass-through query. I think this would perform much better than queries against linked tables.

Duane
Hook'D on Access
MS Access MVP