I've figured out a work around but it is not ideal. It seems it just doesn't like the multiple values pulled into separate columns so what I did was concatenate them all into one field using the '&' and that produced the results accurately. If there is no other solution anyone can help with that...
The V1 is a typo. It should have been F1 which I corrected, but it still doesn't work.
It doesn't seem to like to have the multi values in the output based on the logical evaluation. For example, if I return just one value such as sectype it will give the correct results, but when I try return...
Hi everyone,
I am hoping for help on an IIF statement within a query that is not producing results expected. Analyzing a security table where I self-joined table and I need the results to display on one row per user. The scenario is looking at security values where access is granted using...
Duane
Can you provide an example for the second solution? Mu tables are"
AR
AP
GL
FA
PO
My field in each table is ObjID
How would I construct the syntax for your 2nd solution?
Hello everyone
I need help with a parameter query where I need to pass the table name into the FROM statement at run time. I have a number o ERP modules each in a different table so I just to get he table name inserted when I run it. The query I've tried gives an error. see my code below...
Duane - Thanks.
It is close to working but not sure why I receive an error stating:
"Runtime Error 3078; Microsoft Access database engine cannot find the input table or query "
Make sure it exists and that it is spelled correctly."
Once I click the END button it shows the tables and record...
Hi Everyone,
I'm hoping someone can help me with a query design to count all records (rows) within each table of an Access database. For example, I have about 20 tables and I would like to list the table name (MSysObjects.Name)and the total count of records within each table (count(*) as...
Sorry - for not including in my last post..
How would I create a union query with results that won't be the same output, because I know that one result set will just return a single column of data, while the first result set would be dynamic. Union queries often require the same number of...
Duane - thanks. I've read that posted on here already but can't figure out what exactly do you mean. I'm sorry for being confused on that, but could you elaborate?
Skip -thanks. Here is the code.
Transform count(OUP) as PplCount
Select PositionID, Description
FROM HRData
WHERE HRData.LegacyBU NOT IN (‘N’)
Group by PositionID, Description
Order by 1
Pivot LegacyBU
UNION
Transform count(CCo) as PplCount
Select PositionID, Description
FROM NewCo
Group by...
Hi Everyone.
I'm looking for help in union cross tabs. I need to combine one result that has, for example, 6 columns of results with a second cross tab that has just 1 column of results. The left 2 columns are identical which are in my select statements are indentical.
I receive an error...
Thanks, Max.
While it did work in theory, the problem is that I received an error due to the complexity of the nested IIFs. I have 84 records (thus 84 IIFs) and it produced an error after #6. I really wish there was a way to do a join but given the account numbers are unique, I can't establish...
Hello everyone,
I have a problem I'm trying to solve using SQL to tag a chart of accounts hierarchy file. My COA has several thousand line items and I need to figure a way to tag the account class field to each account in the chart. The problem is I can't figure a way to setup a proper join for...
Hi Everyone,
Is there a way to search and replace a string that is hard-coded in sql queries? I need to change all occurrences of a production server that was hardcoded in the WHERE clause in about 70 sql queries. Not certain how to accomplish this in one swoop.
Any help is appreciated.
Hi Everyone,
I'm struggling to get correct results from a crosstab query with an Inline IF statement. I'm going through a security data file and would like to have results mark 'r' or 'w' based on the security settings. so i have the following code:
TRANSFORM First(IIf(((SECCode ='A'...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.