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 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?
I have a database that is designed with a front and back end, with the FE linked to the BE. Some potential customers want to play around with the database, and the easiest way for me to get them access to it would be for me to combine the FE and BE and just give them a copy of that. how to link a table, and I know how to import a table. What I'm unsure of is how to cleanly convert a linked table to a local table.
I've created a ComboBox and would like one of my selections: "Met" to be converted into a number value: "10" within the related field on the table.I've come up with the expression: =IF([Initial Review Q1]="Met",0,10)
Control Source accepts the expression, however when I go back to Form View and select "Met" access chimes and states: "Control can't be edited it's bound to the expression =if([Initial Review Q1]="met",0,10)".
The table happens to only have one column in it, so each record only has one field..
How do I convert the entire thing to lower case?
I can not just convert the display info to lower case, this table is used as a cross-reference data table and I need to physically convert all the data in the entire table to lower case.
Oh, and I need this to be a macro of some sort, since the data is re-imported on a regular basis, and will re-convert to upper case on the import.
Hello all, I'm quite new to Access. I've read many Access tutorials and site but I have yet to found the solution for my problem. So here it is (pardon my english):
Field AmountTotal is =(Nz([AmountSubTotal])+Nz([AmountSH])-Nz([Discounts]))
I manually input AmountSubTotal, AmountSH and Discounts. I expected an automatic calculation for AmountTotal.
In "Form view" I get the result of AmountTotal that I wanted. But the calculation result doesn't get recorded in my field data table AmountTotal. It just shows blank.
How do I record this automated results into my original table?
I need to import data in this format:The top row of numbers are AttributeIds .In order to import it into my DB I need to convert it into the following fields and records.Any fields that are null should not get a record in the conversion.
I am trying to convert a CSV file that I receive from UPS to a simpler table. I have over 3000 entries from 25 different locations and I need to evaluate the shipping cost. Is there a way in Access to convert the file as attached or should I be trying to use Excel?
Copy that recordset into a Variant variable, similar to a matrix
Run a Heuristic Algorithm on the matrix to populate values
Delete * from the orginal Access table
Input new data into the Original Access table by looping through matrix with an INSERT INTO statement.
This method works but I do not like the loop in step 5 as I am calling an INSERT INTO statement 800+ times and can be slow. Any way to view the variant as a whole and not have to loop through the entire matrix. Maybe possibly converting the variant to an Access tabledef.
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
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;
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;
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;
I am trying to create a table for income and expense [catergory] and would like the amount being entered into the [amount] field to have a negative or positive value on entering based on the category chosen.
so in my table I have
[catergory] which is chosen from a look up table ( which is either an INCOME or EXPENSE ) [amount] which is entered in the next field ( which has to return a positive or negative value based on the catergory choosen upon entering the data)
I would like to go one step further and indicate this negative value in red is possible in my FORM.
I have a multivalue text field called "Groups" that is generated through a relationship with another table called "Comments". When I set this up I left the field as a text field when it should have been a number field, and now I can't use the database with sharepoint, because the field needs to be a number field.
My Question is there a way to convert it without loosing all my data, as there are over 5000 records?
My Table with the multivalue field is called "Contacts", the field is called "Groups".
I tried update and append queries but I must be doing something wrong....
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 |
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))
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;
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
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?
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;
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.
