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
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.
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:
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:
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.
firstly, i have been searching the forum for previous posts on this issue - I was hoping someone else has asked this question before... no luck tho
if someone can point me the right direction that would be great(im guessing it will have to be done through VB) - I wanted to know if it is possible to have a "hidden" field in my primary form, which would be set to autonumber, so that when the user fills in and submits this first form, the hidden autonumber can be passed to subsequent forms.
I need some ideas here. I export my tables to workbook sheets in excel using transferspreadsheet.The different sheets in my workbook are later imported to the corresponding access tables using transferspreadsheet.
1)I want to know how to totally hide my sheets in the workbook so that the users don't tamper with them after export using transferspreadsheet.
Is there any way to keep a combo box hidden until the combo box above it has been selected. Basically I have the user choose a week number from combo box 1 and want to keep combo box 2 with a different list hidden until combo box 1 has been selected. Sorry if this sounds mixed up but its the best way i can describe the problem
Hi, all. I am sorry if i do something wrong but it is my first time here. Situation: I have an application with a login form. Once you press the login button the login form is hidden (visible=False) to be able to pass the userid to another form. In order to close the application the user needs to press a button in the swithcboard but that is not possible because the hidden form.
Question: How can i close the hidden form to be able to close the application?
Was wondering whether its possible to control the appearance of pages... I have a main form on which I have about 7 pages. When the main form is in the FORM-view, the headings of the pages are 'hidden'. One has to scroll up in order to see them. As certain users of the database will be using Access viewer and thus only see this main form, it would be convenient if I could make sure that when the MAIN form is on, the headings are not hidden. So, it's a layout problem. Any tips on this? Thanx Stacey
I have an application in access 2k, where i am hidden access environment using code. Because i am hidding access environment the popup and mode properties are in YES.
Situation: i have a form with a combo list, where i can select a report, 1 button to view the report , another button to print the report and another button to close the form. Once the user has selected the report and pressed the view button the following code runs: DoCmd.OpenReport "reportname", acViewPreview, acWindowNormal
if the user pressed print the following code runs: DoCmd.OpenReport "reportname", acNormal
Problem: When a user wants to view a report nothing happens. the report is not open.
do any of you have an idea?
i am attaching 2 db. One where you can see the problem with hidding environment (wine_NOcode). The second db without hidding it (wine_code). the pwd is 1.
I have searched the forums but all I can find is code to hide the windows taskbar, when actually I want the opposite.
I have a form which I have set the following properties:
Pop-up = Yes Modal = No Control Box = Yes (Only while designing, will change) Max/Min = None
I have also placed code in the On Load event namely "DoCmd.Maximize"
When the form loads, it maximises as I would like but it also covers the windows taskbar, which is something I don't think my users would like at all!!! Any idea how to get it to still maximise ( as this hides the db windows) but not hide the taskbar?
When trying to open an Access database on the server, an error message appears - "Compile of hidden files error", followed then by a run-time error. There is no problem accessing the the server and opening the database from another PC? Does anyone have any ideas what could be causing this. Backups to CD have started failing at the same time.
I have hidden most of the menu bars for my database (from the tools.. startup.. menu) and I dont have a backup of my latest database (yes, I know, stupid.) The only menus I have are File, Edit, Insert, Records, Window and Help. The database window is also not displayed.
How do I get them back..? My database is set to load a form on startup, all data is accessed via a set of forms linked from the first. I cant access the tables, design view, anything. I feel like such a muppet...
Whilst trying to tidy up my database in the Start-Up menu, I checked the boxes that hid all toolbars and menus, so now when I open the database, all I get is the opening Main form I created.
Does anyone know how to correct this please, I need to continue working on the database and it is driving me nuts............
For each field on the report I have hide duplicates set to yes. for each job id most of the line should be hidden except for a couple of fields (part used and description).
This works in 99% of cases but I occasionally I get the whole line repeated
I've treble checked that the fields are duplicates (they are) and am at a loss what to look for.
