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...
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...
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...
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...
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'...
Hi Everyone,
I'm trying to remove duplicate records from a table using one of the queries found in many posts here, but I can't seem to get it to work. I'd like to keep one record of the duplicate set and discard others. Most have just 2 total duplicates, but there are a few with 3 duplicates...
Hi everyone,
I'm struggling to figure out a way to update data based on a crosstab view. My scenario is as follows:
I have 'OBJECTS' as my row data and roles as the header:
I need to update the roles to have the inquiry objects IF they do not have access to the object already. So for...
Hi Everyone,
Is there a way to setup a crosstab query and just place an 'X' in the column where your criteria is met, instead of an aggregate function. I've read through the various threads but could not find anything that addressed how to setup up the TRANSFORM statement to just place an 'X'...
Hi everyone,
I need help with creating a double self join. I am attempting to pull together a security record where there are multiple security types and I need them to all layout in one row. I'm able to get a single self join to work perfectly, however in attempting to get the double self join...
Hi everyone,
I need help with creating a double self join. I am attempting to pull together a security record where there are multiple security types and I need them to all layout in one row. I'm able to get a single self join to work perfectly, however in attempting to get the double self join...
Hello everyone,
I'm having trouble retrieving and sorting by the date column in a query that I've designed to convert julian dates. My query is as follows:
SELECT USER, EVENTDESCR, (DateSerial(1900+(SHUPMJ\1000),1,SHUPMJ Mod 1000)) AS ConvertedDate
FROM HISTORY
WHERE...
Hi everyone,
I'm having trouble with an order by statement where my date is in text format. I've attempted the following but it wouldn't produce the correct results:
Select user, mid(startdt, 3,2) & '/' & right(startdt,2) & '/' & left(startdt, 2) as Start
from Employee
order by...
Hello everyone,
I'm running into problems when trying to append to a table using the following union query. Any help is appreciated:
Select * INTO AU4
FROM
(SELECT Profile, Text, Status
FROM UPR)
UNION
(SELECT Profile, Text, Status
FROM UPR2);
Hi Everyone,
I have an Access DB with more than 70 queries and I need to delete 42 of them in order to hand the db off to some one.
Is there a way to delete a batch of queries at one time using a Delete query statement?
Any help would be appreciated
Hi,
Is there a tool or utility that users are using to format their SQL code when posting in this forum? Is that tool available to all of us who post?
I often note that PHV and others have their code listed in a box and its formatted very nice.
Any information on where I can download or...
Hi Everyone,
I'm trying to extract data from a general ledger system where I need to select a full journal entry which is comprised of several lines based on criteria of at least one line item in an account in an Income Statement range that meets a credit amount value as well as criteria for...
Hello,
I have a query that I wrote to calculate the aging in days of a person's last logon to determine whether they are within Co policy. I would like to order by the aging date descending, but receive errors with every combination I've tried. My query is:
SELECT UserName, UserID, Status...
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.