Hello, im having a real problem running a certain query in access. My database is used to monitor drain spillages, and the query im trying to build needed to look down a column and show descrite water spills. A descrite spill is when water spills for a bit then stops. I need to show how many times this happens in a column.
The complexity of the problem (comparing one value in a column to other values in that column) coupled with the size of the dataset (600K+ rows) just baffles everything I throw at it.
I had two stabs at it. One involved a recordset - looping through and updating a new field. Recordsets can cause database bloat - in this case it bloated the db to 2GB (Accesses maximum size causing the code to crash) - this is something I've never managed before. The other involved a function that left access frozen.
I was really stuck on this until a crazy idea came to mind, i want to know if this would work.
This might sound stupid but how about some crazy thing like adding a new column and numbering the fields, then adding some kind of query that says if the value is over 0.001 then add a 1, if not leave blank. Then have something like if the column has a 1 in it then check the next, if this one has a 1 in it then repeat this step, if not then this is 1 spill. Store the amount it starts and stops to a variable and just use that number as the Number of descrite spills.
If any of you guys have any spare time on your hands would you mind having a look at this mess and tel me where I have gone wrong.http://www.geocities.com/andrewnorthwales/Andrews_database.mdb andrewnorthwales/Andrews_database.mdb mm this board is messing with my url if it puts us.share just change it to www. dont know why my url is not working on hereIm trying toProduce a list of available vehicles and drivers for a given input dateSearch for an availability that satisfies a prospective customer’s criteria (specific vehicle for a specific date)its my first ever database and I cant get the begger working right.it must be relationships or something, but I cant see where I ahve gne wrong.its just a fictional Weding car hire firm and I create records/orders for fictional customers.but its melting my brain __gloop...:confused:
Hi I have created a monster I think and I am not sure how or why. I needed a query to tie together data from a range of tables and so it has about 20 constituent queries, each of which involves some calculation. The query isn't so horrendous SELECT tblApplications.lngApp, qryExpWkHomeHelp.WkHomeHelp, qryExpWkPrescrip.WkPrescrip, qryExpWkHosp.WkHosp, qryExpWkRent.WkRent, qryExpWkSup.WkSup, qryExpWkInt.WkInt, qryExpWkLife.WkLife, qryExpWkCouncil.WkCouncil, qryExpWkCar.WkCar, qryExpWkCar1.WkCar1, qryExpWkHp.WkHp, qryExpWkDep.WkDep, qryExpWkCont.WkCont, qryExpWkProp.WkProp, qryExpWkOther.WkOther, qryExpWkTel.WkTel, qryExpWkTv.WkTv, qryExpWkWater.WkWater, tblApplications.lngAppCon, tblApplications.bAppEleg, tblApplications.dtmAppGcSub FROM qryExpWkWater RIGHT JOIN (qryExpWkTv RIGHT JOIN (qryExpWkTel RIGHT JOIN (qryExpWkOther RIGHT JOIN (qryExpWkProp RIGHT JOIN (qryExpWkInt RIGHT JOIN (qryExpWkLife RIGHT JOIN (qryExpWkCouncil RIGHT JOIN (qryExpWkHp RIGHT JOIN (qryExpWkDep RIGHT JOIN (qryExpWkCont RIGHT JOIN (qryExpWkCar1 RIGHT JOIN (qryExpWkCar RIGHT JOIN (qryExpWkSup RIGHT JOIN (qryExpWkRent RIGHT JOIN (qryExpWkPrescrip RIGHT JOIN (qryExpWkHosp RIGHT JOIN (qryExpWkHomeHelp RIGHT JOIN tblApplications ON qryExpWkHomeHelp.lngExpApp = tblApplications.lngApp) ON qryExpWkHosp.lngExpApp = tblApplications.lngApp) ON qryExpWkPrescrip.lngExpApp = tblApplications.lngApp) ON qryExpWkRent.lngExpApp = tblApplications.lngApp) ON qryExpWkSup.lngExpApp = tblApplications.lngApp) ON qryExpWkCar.lngExpApp = tblApplications.lngApp) ON qryExpWkCar1.lngExpApp = tblApplications.lngApp) ON qryExpWkCont.lngExpApp = tblApplications.lngApp) ON qryExpWkDep.lngExpApp = tblApplications.lngApp) ON qryExpWkHp.lngExpApp = tblApplications.lngApp) ON qryExpWkCouncil.lngExpApp = tblApplications.lngApp) ON qryExpWkLife.lngExpApp = tblApplications.lngApp) ON qryExpWkInt.lngExpApp = tblApplications.lngApp) ON qryExpWkProp.lngExpApp = tblApplications.lngApp) ON qryExpWkOther.lngExpApp = tblApplications.lngApp) ON qryExpWkTel.lngExpApp = tblApplications.lngApp) ON qryExpWkTv.lngExpApp = tblApplications.lngApp) ON qryExpWkWater.lngExpApp = tblApplications.lngApp WHERE (((tblApplications.bAppEleg)=Yes) AND ((tblApplications.dtmAppGcSub) Is Null));
Well maybe it is pretty horrific! However it is taking nearly two minutes to load on a fast PC (3.4GHz Pentium 4 with 1Gb RAM) so put it on a basic desktop and it will die. I did wonder about fragmenting it into four queries and then putting one query at the top of the nest but reckon that's another layer and all the calculations need to be done. Basically the sub queries are calculating a weekly value based upon input and periodicity of payments - i.e. if quarterly then it divides the payment by 13. Any thoughts on how I could speed it up? Thanks in anticipation
Is there any way that I can import a text file and split the data into three columns without having to go through 65000 lines of text.
It is basically a print off of a directory structure. I have a very large text file that lists the files in folders for each of hundreds of users. I need to divide the information up into three columns so that the below data:
Poor old Access. I think I've made it wave a white flag, but based on the following expression I am trying to get it to evaluate, I'm not too surprised...
The situation is that I have a large number of tick boxes on a form, which nicely filter through to a series of fields in a table whose values are either -1 or 0 (according to if the box is ticked or not). Unfortunately, I now need to generate a small piece of text for a report based on which boxes have been ticked. Deep breath...
The logic seemed to work nicely when there were just 3 options in the list, but when I try to enter the above, Access says "the expression you entered is too complex". Poor baby! :D
Does any one have any idea how I can get it to evaluate the data I need?
Hi everyone. I was wondering if anyone could help me. I am in the process of designing a database (Access 2002, office xp), but the problem I am having is that when I am pulling in an ID number, from one form into another form, the form I want this number to land in crashes, with the warning that more than one user is trying to access the form at the same time (they are not, just me).
After having to clear this error box (a few times, it keeps reapearing) errors then appear in each of the destination forms fields. :eek:
I think it is something to do with the way the database is setup in multiuser envirionments and retry intervals etc - and I have been playing about with the values, which seems to work once, but then it goes straight back to crashing. :confused:
If anybody give me some default values that might work :o or any other remedies for this problem, this help would be very much appreciated :)
Many will look at this and laugh but its real. My Access Dbs are opened in hidden state and i dont see my forms. This must be the cause. I tried to hide the menubars using ghudsons code,i disabled the shift key. But i dont know what has happened.
I can nolonger view them. I have tried every method out and i think the only option is to repair OS, may be it will work.
I have enabled the database which i disabled the shift key but when i hold down the shift key, it loads but the forms are totally hidden, STRANGE! ijust see the grey access background and when i click on it twice from the task bar.It pops up.
I have tried to make another database, use the autoexec macro to call a form which enables all command bars, but this works only for this database and when i press the shift key down, this database hides the toolbars and i only see the grey access background. This is happening on EVERY DB ive developed on this machine. I thought it was the access that is totally corrupted but i tried to reinstall my office and i still get the same results This is now a NIGHTMARE to me.
When db is shifted to another machine it works fine as before. This is REAL but can i get some suggestions from you folks before i repair my OS.
I'm using flash mx and MDM zinc which i assume connects using the JET rather than ADO database engine.
For the life of me cannot get a simple update to work due to the dates involved, lost count how many hours have gone by trying!!!
Within access I can easily switch quotes and hash symbols and all SQL THREE SQL queries work without problems on my database i.e.
UPDATE UserOverallResults SET Results = 20 WHERE UserName ='Robbie' AND Quizdate= '19/2/2008 12:15'
UPDATE UserOverallResults SET Results = 20 WHERE UserName ='Robbie' AND Quizdate= "19/2/2008 12:15"
UPDATE UserOverallResults SET Results = 20 WHERE UserName ='Robbie' AND Quizdate= #19/2/2008 12:15#
Within the database the fields are defined as :- Results (Integer) Username (Text) and QuizDate (Date/Time) within MS ACCESS DB schema.
in Flash MX TextDate param is a STRING and All parmaters are populated appropriately (see this later in error message).
Example of existing date data stored in database in format "19/02/2008 16:48:11" as a DATE/TIME field.
My code within in flash is :-
mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " + UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate = '" +textDate + "' "); I've tried with and without Hashes still no joy ..
mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " + UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate = Format( '"+ textDate +"', "#yyyy\-mm\-dd hh:nn:ss#")"); The error message I get from the code above is ..
SQL Query has failed for the following reason: Data type mismatch in criteria expression SQL statement: UPDATE UserResultsOverall SET Results = 30 WHERE UserName = 'd' AND QuizDate = Format('19/2/2008 19:4:35' ,"#yyyy-mm-dd hhnss#")
My text string removes the leading zeros not sure if that would cause an issue or not (its doesnt in MS ACCESS running query) ...
Anyone have any ideas??? Losing my mind here !!
PS I had problems using '&' instead of '+' to concatenate fields.
Im using MS Visual Web Developer with an Access Database, i am trying to enter a new row of data into a database via a select statment attached to a button_click event. the code i am using looks something like this.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
that being said. when i click the button, NOTHING happens, no error and no insert!!!
I did test the connection to the Database and its fine, i can do an update and i can read the data that exists in the database i just cant insert a new row.
This is not the first time i have delt with INSERT statments but this has me at my whits end and any help would be great.
I am working on a project that was started in access, but needs to be moved into an Access project so that It can be integrated with other forms. When I did this I realized that my database queries needed to be changed from Access to SQL code. for example instead of representing a date as #DATE# it needed to be represented as 'DATE'.
However I have one issue that I am having issues with.
I have a query that determines a few collumns by running information through a vb function and returning the result.
The issue is I do not know how to convert this code from Access to SQL so that it will work.
The code is
SELECT Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]) AS [Action], TESTSTAT.*, Action_date([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER])) AS Action_Date_Field, IIf([DUE]<[WAIVER],[WAIVER],[DUE]) AS Later FROM TESTSTAT WHERE [LOG_NO] LIKE '*' ORDER BY Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]);
Can someone provide me with a sutible sql equvalent to this code
I am working on a project that was started in access, but needs to be moved into an Access project so that It can be integrated with other forms. When I did this I realized that my database queries needed to be changed from Access to SQL code. for example instead of representing a date as #DATE# it needed to be represented as 'DATE'.
However I have one issue that I am having issues with.
I have a query that determines a few collumns by running information through a vb function and returning the result.
The issue is I do not know how to convert this code from Access to SQL so that it will work.
The code is
SELECT Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]) AS [Action], TESTSTAT.*, Action_date([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER])) AS Action_Date_Field, IIf([DUE]<[WAIVER],[WAIVER],[DUE]) AS Later FROM TESTSTAT WHERE [LOG_NO] LIKE '*' ORDER BY Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]);
Can someone provide me with a sutible sql equvalent to this code
Can anyone help? I have a query that queries against 11 linked views against an SQL Server backend.
The query is running dog slow, so I want to convert it to a pass-through query so that the processing is done server side rather than Access having to drag thousands of records across the network, but don't know where to start.
The problem is that I need to convert the Access SQL to a version of SQL that SQL Server understands.
Is there a tool I can use that does this automatically (keeping my fingers crossed here)? If not then does anyone know where I can find out how to do this?
This is a Oracle query... its working in Oracle but didnt work in access...
select v.code_number,v.vehicle_number,v.company_code,r.fc _valid_to, i.next_due_date from (vehicledetails v left outer join rtodetails r on v.code_number=r.code_number AND v.code_number='SMR5' left outer join insurancedetails i on v.code_number=i.code_number);
I need equalent Access query for this.... anyone plz help....
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
Is it possible to run a query that's inside an Access database from another program? Possibly excel.
I continue to run into walls with the fact that I can't get my company to buy licenses for Access to put on PCs. I'm determined to work around these issues, but there are obvious limitations.
Right now I got Excel sheets linked to a database where it pulls information of a main database that I'll update. They hit a macro button and it fills their sheet with the information I've provided in the database. At the end of each shift, I pull the information back into the database with the updated numbers for the day with a Union Query, then collect all the data into a new table.
The "old" way was cutting and pasting data from 60 Excel sheets every day which took 2 1/2 hours. I accomplish it now in about 10 seconds. Enough patting on the back.
The problem is that I need to delegate some of this out because I simply don't work all three shifts that we have and I want someone to "pull" the data after each shift. Of course, the people that I want to do this do not have Access.
How can I enable them to run these queries? Is it even possible?
Hi guys im trying to create a access database which has two colums like the example shown below.
Registry total registry
/// 3
basically the first colume would contain these symbols /// and i want the second colume to read off the first colume soo if I have /// the total registry colum would show the total number of ///.
how woud i get the tables within access to do this:confused:
Hi,I am new to database work.I have to write a query to design a report basaed on the data coming from raw table.I have a table which contains sales information like deal name,code,closing date of deal,total revenue for the year and probability.I have to write a query to get the new data based on the table containing raw data. My query should contain monthly revenue and quarterly revenue based on the fields closing date and total revenue.ex:If the closing date for a particular deal is mar-08 then the total revenue entered in revenue field should get updated in a new field [march month].Please help me how to build a logic for the query.I have to design a report based on this information and source should be one table.
Any help would be appreciated...
pls help me thanks in advance...
below table givesidea to build a query: Closing date Jan Feb Mar Apr May Jun Revenue Q1 Q2 Q3 Q4
How can i find max date and close value corresponding to that date. The following query doesn't work as A.Close is not a part of aggregate function and using group by on A.Close gives undesired results.
SELECT MAX(A.Date), A.Close FROM (tblSecurityDay A INNER JOIN tblSecurity B ON A.SecurityID = B.SecurityID) INNER JOIN CompanyDetails C ON B.SecurityCode = C.CompanyCode AND B.CompanyKey = C.CompanyKey WHERE C.CompanyCode = 'ABC' AND C.CompanyKey = 1
I have recently started working on a Access database and I have very little knowledge about it. This is what I am trying to do: I made a form with 4 text boxes on it. The user fill sout the info in these text boxes and then hit a button. What i want to happen is:
One of the fields is actually a criteria for a query. Depending on what the field entry is, I want to make a report will all the records that match the criteria. eg: if the user types in 278 for a lot # criteria (one of the boxes on the form) then, i want all records that match that lot # to appear in a report.
I really have been struggling with this. Please help. Thanks ~svn
I have recently started working on a Access database and I have very little knowledge about it. This is what I am trying to do: I made a form with 4 text boxes on it. The user fill sout the info in these text boxes and then hit a button. What i want to happen is:
One of the fields is actually a criteria for a query. Depending on what the field entry is, I want to make a report will all the records that match the criteria. eg: if the user types in 278 for a lot # criteria (one of the boxes on the form) then, i want all records that match that lot # to appear in a report.
I really have been struggling with this. Please help. Thanks ~svn
Im importing a file into access and i want to add querys. The problem is the field im querying wont always be in the same place or be the same name. The only thing that remains the same is that the field i want to query will always be the last column. Anyone know what to do for this??
Hi i was hoping someone can help me with this, i am using access 97, i have a table from which i am trying to generate a query , basically what i want to do is for the query to show stock that is less than or equal to a percentage of a field called prefered stock, for example, if say my prefered stock level is say 200 units, when i run the query i want it to show all products whose stock levels are less than or equal to 40 percent of the prefered level, enabling me to see what needs to be ordered , however i do not know what criteria to add into the query to do this or how its done , say my field name is prefered_level and i want to show all products that are 40% of prefered level do you knwo how i would phrase this in the criteria or can access handle such a calcualtion, any help is much appreciated thanks j
Can someone show me how to convert this SQL query to an ACCESS query? - I have tried several time with no luck. Thanks! Mike
Select COUNT (b.case_id), sum(a.amt_to_distribute) Held_amt, iv_d_do_code, cd_reason_status 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 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 AND A.Id_case = b.case_id ) Or ( trim(A.Id_Case) IS NULL 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;
I have patients with temperature readings every minute. Some last an hour some last multiple hours, some last days. There could be 100, 500, or thousands of values. I need to find the 10th value and 10 values before the end of the value list. So if someone had 60 temperature readings, I need the 10th and the 50th. Is this possible or do I need more tools? If so do you have any examples?
Hi,I am new to database work.I have to write a query to design a report basaed on the data coming from raw table.I have a table which contains sales information like deal name,code,closing date of deal,total revenue for the year and probability.I have to write a query to get the new data based on the table containing raw data. My query should contain monthly revenue and quarterly revenue based on the fields closing date and total revenue.ex:If the closing date for a particular deal is mar-08 then the total revenue entered in revenue field should get updated in a new field [march month].Please help me how to build a logic for the query.I have to design a report based on this information and source should be one table.
Any help would be appreciated...
pls help me thanks in advance...
below table givesidea to build a query: Closing date Jan Feb Mar Apr May Jun Revenue Q1 Q2 Q3 Q4
