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 Replies
ADVERTISEMENT
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
Apr 1, 2015
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.
View 2 Replies
View Related
Jun 28, 2005
I have a txt file that includes X and Y variables like below, with a comma seperating the x from the y.
1.5,2
1.9,30.25
100.00,5.23
4.16,12.3
10.23,14.5
..
....
..... up to an unspecified number
how can I import the txt file data and place that data into a table.
A GPS device creates the txt file that will be placed in a folder.
thank you in advance
View 5 Replies
View Related
Oct 15, 2012
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)".
View 3 Replies
View Related
Sep 24, 2007
I need to convert an entire table to lower case..
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.
Thanks,
Bill
View 2 Replies
View Related
Jan 1, 2006
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?
Thanks in advance!
View 4 Replies
View Related
Aug 11, 2015
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.
View 3 Replies
View Related
Jan 29, 2013
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?
View 2 Replies
View Related
Aug 20, 2013
Creating a recordset from an Access table
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.
View 2 Replies
View Related
Feb 17, 2014
I have a problem in converting the content of a simple table to a text format I need for an other program. The table is containing 3 columns
- ITEMID
- COLOR
- MINQTY
How do I convert the table to a text format with below format.?
ITEMTYPE is all ways >P<
How is this text shown. Do I make a kind of report.?
I have attached the two document (Text format and DB)
<INVENTORY>
<ITEM>
<ITEMTYPE>P</ITEMTYPE>
<ITEMID>3622</ITEMID>
<COLOR>11</COLOR>
<MINQTY>20</MINQTY>
[Code] .....
View 1 Replies
View Related
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 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
Jun 1, 2013
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.
View 3 Replies
View Related
Oct 21, 2012
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....
View 2 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
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
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
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
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
