I Have A Sql Query Pls Convert It To Ms Access Query

Mar 12, 2007

I have made this query in ms sql it works fine as per my needs iam getting the values but this doesn work in ms access 2002 can someone work this out its lil urgent tested out a lot of things but better to give the best people's n get their knowledge...

SELECT wc.windowsname FROM windowsconductivity as wc join constructiondetailes as cd on wc.windowsid=cd.materialid join uvalueconstruction as uc on uc.constructionid=cd.constructionid join projectmasterwindows as pmw on pmw.windowconstruction=uc.constructionname WHERE pmw.projectname='new8thmarch07' and pmw.windowconstruction='new8thmarch' and uc.constructiontype='windows' order by cd.constructiondetailsid

thanks aton...

View Replies


ADVERTISEMENT

Convert And Sql Query To Access

Sep 15, 2007

someone wrote this query for me in SQL and there is one part of it I don't understand can someone tell me how to convert this line for access? I don't know what || is

TO_NUMBER(SUBSTR(TO_CHAR(WE.mth_welfare),5,2) ||
SUBSTR(TO_CHAR(WE.mth_welfare),1,4)) an_mth_welfare


SELECT
WE.mth_welfare,
WE.id_case_welfare,
WE.id_case,
OB.id_member,
SUM(WE.amt_mtd_assist_expnd),
SUM(WE.amt_ltd_assist_expnd),
SUM(WE.amt_ltd_assist_recoup),
TO_NUMBER(SUBSTR(TO_CHAR(WE.mth_welfare),5,2) ||
SUBSTR(TO_CHAR(WE.mth_welfare),1,4)) an_mth_welfare
FROM noldba.welfare_balance_by_obligation WE, noldba.obligation OB
WHERE WE.id_case = OB.id_case
AND WE.seq_order = OB.seq_order
AND WE.seq_obligation = OB.seq_obligation

AND WE.mth_welfare <= 200708
AND WE.dt_END_validity = '31-DEC-9999'
AND WE.dt_end_validity = OB.dt_end_validity
AND OB.dt_beg_obligation = (SELECT MAX(c.dt_beg_obligation)
FROM noldba.obligation c
WHERE c.id_case = ob.id_Case
AND c.seq_order = ob.seq_order
AND c.seq_obligation = ob.seq_obligation
AND c.dt_end_validity = '31-DEC-9999')
GROUP BY WE.id_Case, OB.id_member, WE.id_case_welfare, WE.mth_welfare
ORDER BY WE.mth_welfare DESC,
WE.id_case DESC,
OB.id_member DESC;

View 3 Replies View Related

Convert SQL Query To Access

Dec 18, 2007

I have this query which I use with a program called "Query Reporter" - it runs well and gives me the correct answers.
Select COUNT (b.case_id),
sum(a.amt_to_distribute) Held_amt,
iv_d_do_code,
cd_reason_status--HELD REASONS-DH-DIST,NX-NO OBL,XS-EXCESS OBL,MW-MANU WKR,HM-NO MHIS,CC-CLOSED
From vrcth a, vcase b
Where A.Cd_Status_Receipt = 'H'
And A.Dt_Distribute = '01-JAN-0001'
And A.Dt_End_Validity = '31-DEC-9999'
AND Not Exists (Select 1 from vrcth k -- Checking receipt is not backed out
where a.dt_batch = k.dt_batch
ANd a.no_batch = k.no_batch
And a.cd_source_batch = k.cd_source_batch
And a.seq_receipt = k.seq_receipt
And k.ind_backout = 'Y'
And k.dt_end_validity = '31-DEC-9999')
And ((trim(A.Id_Case) IS NOT NULL -- When receipt is case identified
AND A.Id_case = b.case_id )
Or
( trim(A.Id_Case) IS NULL -- When receipt is payor identified
ANd b.case_id = (Select Min(y.case_id) from vcase Y, vcmem Z
Where A.Id_Payor = z.member_id
And z.relation_code IN ('A','P')
And z.case_id = y.case_id )
))
group by iv_d_do_code,
cd_reason_status
order by iv_d_do_code;

What I want to do is to rewrite the query in Access and get the same results. This is what I came up with which is returning nothing. Would someone please give the two of them a look and tell me what I'm missing the in Access query. Thanks

SELECT Count(b.CASE_ID) AS CountOfCASE_ID, Sum(a.AMT_TO_DISTRIBUTE) AS Held_amt, b.IV_D_DO_CODE, a.CD_REASON_STATUS
FROM NOLDBA_RECEIPT AS a, NOLDBA_INT_CASE_STATUS AS b
WHERE (((Trim([a].[id_case])) Is Not Null) AND ((a.ID_CASE)=[b].[case_id]) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_DISTRIBUTE)=#1/1/2001#) AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k
where a.dt_batch=k.dt_batch
and a.no_batch=k.no_batch
and a.cd_source_batch=k.cd_source_batch
and a.seq_receipt=k.seq_receipt
and k.ind_backout ='Y'
and k.dt_end_validity =#12/31/9999#))=False)) OR (((Trim([a].[id_case])) Is Null) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_DISTRIBUTE)=#1/1/2001#) AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k
where a.dt_batch=k.dt_batch
and a.no_batch=k.no_batch
and a.cd_source_batch=k.cd_source_batch
and a.seq_receipt=k.seq_receipt
and k.ind_backout ='Y'
and k.dt_end_validity =#12/31/9999#))=False) AND ((b.CASE_ID)=(select min (y.case_id) from NOLDBA_INT_CASE_STATUS Y, NOLDBA_INT_CASE_MEMBER Z
WHERE A.ID_PAYOR=Z.MEMBER_ID
AND Z.RELATION_CODE IN ('A','P')
AND Z.CASE_ID=Y.CASE_ID)))
GROUP BY b.IV_D_DO_CODE, a.CD_REASON_STATUS
ORDER BY b.IV_D_DO_CODE;

View 3 Replies View Related

Trying To Convert SQL Query To Access, Don't Know Where To Start

Jul 12, 2005

I just started working for a company the other week and they threw this query onto my lap with an unrealistic deadline. My programming experience is primarily in VB6, so I am not completely familiar with coding Access queries.

I was given code that was written for SQL, and the programmer that came up with it is unfamiliar with Access, so I am stuck trying to decipher this. If anyone could help me out, I would greatly appreciate it, as I am thoroughly confused and don't know where to begin. Here is the programmer's original code:
while exists(select top 1 accNum from NoteFile where patindex('%' + char(10) + '%',dbtrref) > 0)
begin
--Insert note into table
insert into #TL19Note(AcctNumber, Note)
select accNum, (case when patindex('%' + char(10) + '%',
dbtrref) <= 0 then dbtrref else left(dbtrref,patindex('%' + char(10) +
'%',dbtrref)) end)
from NoteFile

--Delete note from Note String
update NoteFile
set dbtrref = right(dbtrref,len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) + '%',dbtrref))))
where len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) +'%',dbtrref))) > 0

--Delete rows with no more notes
delete from NoteFile
where len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) +'%',dbtrref))) <= 0 or patindex('%' +char(10) + '%',dbtrref) <= 0
end

--Eliminates any that are CRLR or just spaces
delete from #TL19Note
where len(Note) < 10

--Update the date
update #TL19Note
set NoteDate =left(Note,patindex('% %',Note)-1),
Note = right(Note,len(Note)-patindex('% %',Note))

--No '@' means no time?
update #TL19Note
set NoteTime = '0000'
where left(Note,1) <> '@'

--delete the '@' - we know which ones have it because NoteTime is null
update #TL19Note
set Note = right(Note,len(Note)-patindex('% %',Note))
where left(Note,1) = '@'

--Update NoteTime, take time out of note
update #TL19Note
set NoteTime = left(Note,2) + substring(note,4,2),
Note = right(Note,len(Note)-patindex('% %',Note))

View 6 Replies View Related

Convert From Access Query To MS SQL View

Oct 21, 2005

Hi folks,

I have a query which pulls together several other queries. It works fine in Access but I also need it to work on a MS SQL server.

the SQL is as follows;

SELECT Client.ClientName, Entity.EntityName, IIf([101].[ProductID] IS NULL, [-], [X]) AS TA, IIf([301].[ProductID] IS NULL, [-], [X]) AS TR, IIf([302].[ProductID] IS NULL,
[-], [X]) AS CU, IIf([304].[ProductID] IS NULL, [-], [X]) AS TC, IIf([502].[ProductID] IS NULL, [-], [X]) AS FA, IIf([503].[ProductID] IS NULL, [-], [X]) AS MO
FROM Client LEFT JOIN
Entity ON Client.ClientID = Entity.ClientID LEFT JOIN
VIEW_PROD301 AS 301 ON Entity.EntityID = [301].EntityID LEFT JOIN
VIEW_PROD302 AS 302 ON Entity.EntityID = [302].EntityID LEFT JOIN
VIEW_PROD304 AS 304 ON Entity.EntityID = [304].EntityID LEFT JOIN
VIEW_PROD502 AS 502 ON Entity.EntityID = [502].EntityID LEFT JOIN
VIEW_PROD503 AS 503 ON Entity.EntityID = [503].EntityID LEFT JOIN
VIEW_PROD101 AS 101 ON Entity.EntityID = [101].EntityID;

The problem seems to be with the iif statement.

Any ideas how I can gt around this?

Thanks,

Dave

View 3 Replies View Related

Help CONVERT Access PIVOT QUERY To SQL SERVER

Oct 22, 2007

Can anyone help me convert this pivot query to work in sql server please?
I'll love you forever if you help me please :pTRANSFORM Count(Employees.MaritalStatus) AS MaritalStatusCount
SELECT Employees.MaritalStatus
FROM Employees INNER JOIN Offices ON Employees.OfficeId = Offices.officeId
WHERE ((Not (Employees.MaritalStatus) Is Null))
GROUP BY Employees.MaritalStatus
PIVOT Offices.officeLocation;

View 2 Replies View Related

How To Convert Number Date To Text In Access Query

Feb 12, 2015

how to convert number date to text date.

Ex. Date field: 02/12/2015

convert into:

Month field
February

Day Field
12

Year
2015

View 3 Replies View Related

Queries :: Convert String Back To Hyperlink In Access Query From SQL Table?

Nov 10, 2014

I am in the process of building a new database in SQL to replace my MS Access database. However, I will continue to use the Access forms, queries, and reports. The new tables will house much of the same data. In multiple tables I have hyperlinks that were created and added in the original Access tables. To import these hyperlinks into the new SQL tables I have converted them to 'Long Text' before exporting, thus changing them into strings.

For example:

Hyperlink - Email - Add Additional Mailbox to Outlook (2010) has been changed to:

Email - Add Additional Mailbox to Outlook (2010)#ServernameServerfolderDocumentationRea dy to GoOutlook TemplatesEmail - Add Additional Mailbox to Outlook (2010).oft#

The obvious issue that I am running into is that after the SQL database table has been linked to the Access database it still displays the entire string when I open the table. The form has a textbox and search button that is used as a search function. This runs a query that returns all "search results" for the desired information. Is there a way that the query can convert the string back into a hyperlink so that the query displays just Email - Add Additional Mailbox to Outlook (2010) as a hyperlink and not the entire string?

View 1 Replies View Related

Convert Query To Table???

Nov 8, 2005

I have a (crosstab) query that summarizes information in the way that I need for a module that I have written. However, I've noticed that the each time the module tries to pull a value from the query (every step of a Do loop) it takes seconds, compared to a "real-time" pull from a table. I have had a program running for about 2 days now, whereas a similar program that pulled data from only tables took only 15 minutes. I don't forsee needing the data in its original form.

How do I convert a query to a table? (Export the query to Excel and then import.)

I would appreciate any ideas. Thanks.

View 1 Replies View Related

Queries :: How To Convert 5 Into 1 Query

Jul 21, 2015

I have three tables in a database horse, entry, prize and I am supposed to get the result by one query. A list is required of horses that have career winnings better than that of the average horse. List the horse_id, horse name and the career winnings.

A career winning is the sum of all money won by a horse. The solution must include horses that have not won any prizes when calculating average winnings. Remember to exclude the dummy horse entries for Unknown Sire and Unknown Mare.It is way too complicated for me to get the result by one queries as its nested critera.my solutuin is to create 5 queries with each one getting to the result a bit closer.bit by bit, basing on every previous query, my final query gets the result.Below are my 5 queries in numeric order:

1:SELECT entry.event_id, entry.horse_id, prize.Money, horse.Horse_name FROM horse INNER JOIN (prize INNER JOIN entry ON (prize.event_id = entry.event_id) AND (prize.place = entry.place)) ON horse.Horse_id = entry.Horse_id;

2:SELECT horse.Horse_name, [careerwinningssub1].horse_id, Sum([careerwinningssub1].Money) AS careerwinnings
FROM careerwinningssub1 INNER JOIN horse ON [careerwinningssub1].horse_id=horse.Horse_id
GROUP BY horse.Horse_name, [careerwinningssub1].horse_id
ORDER BY [careerwinningssub1].horse_id;

3:SELECT horse.Horse_id, horse.Horse_name, careerwinningssub2.careerwinnings
FROM horse LEFT JOIN careerwinningssub2 ON horse.Horse_id = careerwinningssub2.horse_id
GROUP BY horse.Horse_id, horse.Horse_name, careerwinningssub2.careerwinnings
HAVING (((horse.Horse_name) Not Like "unknow*"))
ORDER BY horse.Horse_id;

[code]....

View 10 Replies View Related

NEWBIE: How Do I Convert A Query To Text?

Mar 15, 2006

Hi,

I have a database with a query that returns a list of printers. This is created by finding all the printers in the database that take a perticular cartridge by code. Sample below.

PrinterName
-----------------
Some Printer 1
Some Printer 2
Some Printer 3
ETC

What I want to do is take the results from the query and convert it into a single line of text and place it into a column of a table related to that cartridge code.

So if say the cartridge code '12345' returns a list of printers such as;

printer 1
printer 2
printer 3

I want to take this data and turn it into a line like this;

printer 1, printer 2, printer 3

I then want to take this new line and place it in a column called Desc in another table relative to the original cartridge code.

table should look like this after query.
BEFORE:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 |


AFTER:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 | printer 1, printer 2, printer 3

I hope what I have said makes sence. I am pretty new to access and feel I have done quite well so far. Please can someone help me solve this problem?

Thank you

View 4 Replies View Related

Convert Query Data To Other Format

Nov 13, 2005

I am getting the following from a query

date|id1_|id2___|
1___|nick|james|
2___|john|nick_|

How can i convert this data this


id_____|date1|date2|
nick___|_1___|_2___|
john___|_1___|_____|
james_|_____|_2___|

Best Regards

View 4 Replies View Related

Convert List In Query, Like IIF But With Two Lists...

Apr 14, 2006

Hi,

I need to output data in a specific format for a certain client..
for example where

Select * from Falls - would normally give me:

01/01/05 Joe Smith ICU, Assisted

I need in this intance to get:

01/01/05 Joe Smith 5, 2

where locations, or types etc are converted to a corresponding number.

I can imagine a query like:

Select FallDate, PatientName, somefunction([Location],(MS2,ED,OB,CAFE,ICU),(1,2,3,4,5)), somefunction([Fall_Type],(Accidental,Assisted,Unanticipated),(1,2,3))

Is there some more powerful version of IIF or way to write this in the query without having to create a report with a bunch of functions?

View 5 Replies View Related

Convert Number To Text In Query

Dec 1, 2007

I want to have a question in my query that does not get answered by date but by a concatenated field named "MonthYearPeriod".

We always forget how many days in a month. But we remember the month and year. So for say a Sales Range of Nov 2006 to Oct 2007, we prefer to respond to the criteria question as: 11-2006 and then 10-2007.

FiscalMonth and FiscalYear are both Long Integer number fields in the table.

Query field concatenated:
MonthYearPeriod: [FiscalMonth] & "-" & [FiscalYear]

criteria line:
Between [Month-Year START DATE] And [Month-Year END DATE]

However, i get other crazy time periods. So this is not working. Maybe on the Query field box, I need extra coding to convert the number to text?

I assume that a Dash sign between the month and year are fine.
Thanks

View 5 Replies View Related

How To Convert Database (query) To Web Page

Feb 24, 2013

1. how to convert an access database (query) to a web page.
2. what knowleage we should learn ?(php,asp....)

View 2 Replies View Related

Convert String To Date In Query

Jun 21, 2015

I have a string value that comes out of a query in the form of

01012015
02012015
12012014

etc...

where the first 2 digits are the month, the next 2 are the day, and the next 4 are the year.How do I convert these values to a valid date that can be queried on?

View 14 Replies View Related

Query Selects No Records, How To Convert Null To Zero

Apr 29, 2008

Simple query, but I need to roll the results up with others and a null return is hurting me. I have been chasing my tail around with the nz(), input masks, IIF statements, only to prove my ignorance and possible insanity for expecting different results.

Here we go:

SELECT Count([LDB LOG].DSTHTL) AS ["tot120"]
FROM [LDB LOG]
WHERE ((([LDB LOG].[LDB Rcvd]) Between Now()-120 And #1/1/2008#))
GROUP BY [LDB LOG].[T-7 Date]
HAVING ((([LDB LOG].[T-7 Date]) Is Null));

This is being used to count open items into age buckets, the above is the greater than 120 days (discounting anything prior to 2008). If the T-7 Date is null the item is open. What is happening is that no records are being selected, resulting in the null value back.

Any help would be greatly appreciated as I have spent an unrealistic amount of time on this.


Thanks
Brendan

View 6 Replies View Related

How Can I Convert Table1 To Query Or Table2 As Attached ?

Jan 10, 2005

Hi all,

Could you help me in converting table1 to query or table2 as attached?

Regards,

Mohammed Dallag
Saudi Arabia

View 1 Replies View Related

Queries :: Convert YYMMDD TO DDMMYYYY In A Query?

Sep 9, 2013

how I would convert YYMMDD TO DDMMYYYY in a query? Here is an example of the date I am trying to convert.. 130820 - I want to display 20/08/2013. T

View 3 Replies View Related

Convert Date To Its Numerical Equivalent In A Query?

Sep 23, 2014

How can I convert a date format to it's numerical equivalent inside of a query? For instance, 1/1/2014 to be 41640.

View 2 Replies View Related

Queries :: Can Query Convert A Field Value Into Number Of Records?

May 11, 2013

I build a database to manage some events. To simplify things, let's assume an event takes place on 3 days, from 01-05-2013 to 03-05-2013.

I create a table named EVENTS.

Among other stuff, I create the following fields:
EVENT_first_date
EVENT_last_date

Then I create a query which returns the number of days the event will take place on.

EVENT_total_days: [EVENT_last_date]-[EVENT_first_date]

I use an append query to insert this new record into the table EVENTS_MAIN.

I wish to set the start time and end time for all event days in hours and minutes. I will use these entries for displaying start time and end time for each event days, to calculate daily total hours and event total hours later on.

Therefore, I wish to create a query that will display event date 1, event date 2 and event date 3, based on the number of the EVENT_total_days field, so I can set the start time and end time for each day separately.

Is it possible to "convert" or "switch" the number of the days into number of records? Can a query convert a field value into number of records?

View 9 Replies View Related

Modules & VBA :: Query Loop - Unable To Convert To Form Reference

Sep 9, 2013

I have a form with 10 combo-boxes. Once user selects a value in CB1- CB2 becomes visible and active. I am trying to run a dynamic query- where the selection of the combo-box is used to select a column from my table called "dbo_animals"

To elaborate: CB1 contains the following values

Code : elephant, giraffe, bufffalo, tiger, lion

Once the user selects elephant in CB1, CB2 becomes active and I select tiger next. So the query becomes like. The process can go on and the user can select up to 10 animals

Code : **SELECT elephant, tiger FROM dbo_animals**

Problem: I am able to create the query with string manipulation- Unfortunately due to the way the loop through controls is set up- The query is unable to convert the text into a reference. If I hard code it as

tempquery = "SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals" MsgBox(tempquery)

It looks like

Code : SELECT Elephant from dbo_animals

This is how I want. But, since i am looping through CB controls, I have it set up as

Code : tempquery = "SELECT" & " [Form]![Animal Finder]![CB" & i & "] FROM dbo_animals"

and this shows up as

Code : SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals

Thereby giving me an error saying that the reference is not valid and asking me for a parameter value.

This makes sense, since it is unable to evaluate the text as a reference.

How do I correct the text into a reference? Or How do I build a query with adjusting columns based off selections from a combo-box and loop through the comboboxes in the form while auto-updating the query?

View 4 Replies View Related

Convert Access 2000 Database To Access 2003

Sep 27, 2006

Hi,

A few years ago I’ve made an access database on access 2000. The problem is that now I’ve installed the office 2003, and I can’t make the database work properly.

At first says that are missing those files: “tshoot.ocx”, “msioff9.ocx” and “msowc.dll”; so, I’ve copy them from another computer using office 2000 and now only complains about “msioff9.ocx” saying that my database contains a reference incomplete to it.

Is there no other way to convert my database do work properly with access 2003? What can I do?

Regards

View 2 Replies View Related

Convert Text Field To Number Field In Query

Nov 18, 2005

As the title says, i have an identifier field which combines two codes eg. AAAAAA/1234.

I am using a query to extract the number part, however when i try to link to another query it says 'type mismatch'.

I assume that this is because the main query is based on a table where [ShipID] is numeric and the extracted data is based upon a underlying table where [PackageID] is a text field.

Is there any way to get round this?

Many thanks

View 2 Replies View Related

Convert A Text Field To A Number Field In A Query

Feb 28, 2007

Hi,

I have a text field in a table and need to convert it in a query to a number field for sorting purposes, but not have it change in the table.

Thanks,

Jeff

View 3 Replies View Related

Convert Input Box String To "Yes/No" Value In A Query

Jan 12, 2007

I'm trying to create an update query that prompts the user to enter the "Update to" value. The field I am trying to update is a "Yes/No" check box field. When using the following code it because I don't have quotes around the default value "Yes" it inserts "-1" so it works, however the users of the database don't/won't know that "-1" is a yes value and "0" is a no value, so I'd prefer that they could type in text as "yes" or "no" instead:

Eval("InputBox(""Do you want to Complete Update Check Box?"",""Updates Completed?"",Yes,4000,2000)")

Is there a way to convert the "Yes" entered into the Input Box by the user to a "-1" or "0" value behind the scenes so that it will update the field in my table?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved