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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
