Hi
How is it possible to change row in query column title?
I have query which look like this:
[number] [start time] [end time]
1224345 12.6.2007 13.6.2007
I want that my query should look like this:
[number] [start [end
time] time]
1224345 12.6.2007 13.6.2007
I am creating a database that is going to import excel files once a week. Most of the data will be the same every week, but some may change slightly or new records may be added. I need to create a query that will show all the changed/new records that are imported from the excel file. i would prefer to use as little code as possible if at all. I am pretty much a novice when it comes to queries so any suggestions would be appreciated.
View 1 Replies View RelatedI'm a beginner Access user and would love some advice.
I have two queries:
1. A query called: Scores
This query tallys the information (in numbers) gathered from different fields from a table in a raw score.
2. A query called: Results
This query should then convert the raw score from a field in the query Scores and display a sten score (1-10) in the query Results depending on some pre defined values. For example: A raw score of 1-21 should return the sten value 1. From 22-38 should return the value 2 and so on and so forth.
Somebody remarked that I should use Select Case to do this, but I cannot find an example of how to use that that is easily
tranferable to my circumstances.
Thanks!
I am trying to change the button color on a subform if a related form data changes.Main form is products with a continuous subform with serial numbers of products i.e, serial number, location, price and a button to add addtional issues if there are any for this particular serial number (this will open up another form related to the serial number so I can add an issues if there are any).The reason I would like the button to be a different color is so I can quickly see if there are any additional notes been added to the serial number. Just in case you may ask why not add the field to the continuos form is that the issues and be quite lengthy and there may be lots of serial numbers on the form
2346 location warehouse price 29.99 (button - green)
2347 location shop price 29.99 (button - red)
Another one I'm struggling with.
I have a mainform and a subform. What I want to achive is :
click on a lable (lab1) in the mainform and set the recordsource ( query1 ) in the subform to display relevant data.
Click on a lable ( lab2) in the mainform and set the recordsouce (query2) having different data than query1 in the subform.
Hope this make sense.
How would I do this.
I have a database consisting of one table with 5 columns, called Name, Zip Code, Model, Serial #, and Reference #
I made a form with 5 text boxes (generically named Field 1 through 5), and then I built a query. I put all five of the table's fields in the query. In the criteria fields of those five columns, I have
Criteria: Like "*" & [Forms]![Search_form]![Field1] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field2] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field3] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field4] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field5] & "*"
However, here in lies the problem:
Some of the records don't have a reference field included, and others don't have serial numbers, and etc. Basically, some records have blank fields.
So to find all of "John"s records, we would put John in Field1, and then run the query.
The first criteria is now looking for *John*, which is correct (it might find Jack John, Johnny, John John, or anything else with j o h n in it).
The rest of the criteria are now looking for **.
However, what about the records with blank fields?
John ; _____ ; SDMS71 ; 1231234 ; REF9001 will NOT make it into the query, because that blank field for some reason doesn't meet the ** wildcard!
how can I accomodate this "blank field" problem? I want those records with blank fields to still be included in the query, because they still belong to "John", and that's what i wanted to search for! :(
I have a database with 20 queries designed specifically for my department (SAM). A friend would like to use my database i.e. queries, reports, etc., but her department is named different (PAM).
The the field names in the each of our imported 'data' is the same, EXCEPT, each record in her department data field has "PAM" mine "SAM". However, her department is not part of my data import nor mine of hers nor will they ever be.
How can I, except manually, change every instance of "SAM" to "PAM" in the 20 queries?
Thanks
Gunner...:confused:
I have a report based on a query
and it hase a [Product] and a [Quantity] field
Quantity Product
80 F2 1.25" Fiberglass Sucker Rod
60 F2 1" Fiberglass Sucker Rod
80 1" coupling
60 7/8" coupling
what i am trying to do is multiply the quantity by 37.5 if [Product] equals one of the items that are sold by the foot and leave the other items the same
this is the way it needs to display on the report
Quantity Product_Name
80 (3000') F2 1.25 Fiberglass Sucker Rod
60 (2250') F2 1 Fiberglass Sucker Rod
80 1" coupling
60 7/8" coupling
thanks for any help
Hello,
I have a form that has a check box on it. I have a query that calls all records where the check box is checked.
Once I peform my required action using the query I want to change all of the check boxes back to "Not Checked"
Is this possible? Currently I open the query and manually uncheck them.
Thanks!
Issue: We have software that maps some of our "custom" grades that we use locally to the state-specified grades when we submit.
Ex: Grades PA and PP map to report to the state as PK. The data stays in our software as PA and PP however as it is needed that way.
I'm trying to run queries comparing our data with what is at the state to head off mismatches and want to find a way to NOT have these grades show a false mismatch. The field for flagging as mismatch is for example
GX = iif ([schoolgrade] not like [stategrade], "GRD", null)
I tried doing a compound iif statement in my comparison query field but got stalled there as there are 6 or 7 grades that map, one with 3 that maps to one state grade.
What would be the best way to alleviate these? I know I can do a change query, but I'm assuming that can only be done one at a time, and I would have to import the data. Doing find and replace is too manual, and the data is updated daily so I would want something automated.
At this point I'm thinking of creating a bunch of change queries and automating the running of them but that seems like the long way to do it...
All,
Is there a way to create a query to show when text in a specific field was changed?
I wondered if someone could advise?
We have recently changed our SQL Server and I am now trying to point an access database to this new server using ODBC connections.
I have updated my ODBC settings and altered the name to the new object. It stills seems as though the database is pointing to the old tables though as receiving an error message:
"The microsoft jet database engine could not find object....."
Does anyone have any suggestions?
Thanks
Hi
I've built a macro that runs through about 12 queries one after the other, which I run every day. They basically bring in data from a data warehouse held on a Pervasive server through an ODBC link, then perform calculations based on other criteria in tables held within the access database. Up until about 10 days ago, the whole thing took about an hour to run. Then all of a sudden, this changed and it now takes around 5 hours. I haven't made any changes to the macro or any of the queries which it runs. I was wondering if anyone has any ideas what might have caused this (as it's now almost unusable). I've compacted the database to remove bloat, added more RAM, moved the Access DB onto my C drive instead of the server, stopped my antivirus program from looking at either my access DB or the datawarehouse where the data comes from, pestered the network guy to find out if they've changed anything to do with the server (he says no). Any ideas for other things to try would be great, as I'm on the verge of throwing my PC out of the window in case that helps!
Many Thanks
Andrew
quick question. If i run a query and change some data in the query will in also change the data in the table? I tried it and it in fact did. If this really does work I don't think it should be allowed to functionally do that since people can easily mess up the data in the table.
View 4 Replies View RelatedOk, been puzzled by this one.
I have a form with a subform in it. The subform is based on a query. It shows all my active staff. On the form i have a button that (atleast i would like that it does that) when clicked it sets the field "selected" on true on the subform.
The code behind the button works just fine, but i always get an error stating, can't change data, database or object is read only. Funny thing is, when i click on the checkbox "selected" in the subform behind each staff member, i can set it to true. Why does it not work? I tried it with selecting the query itself (qerWerknemersActief instead of tblWerknemers) and also not working. Here's the code, hope someone has a clue. Oh, in each form everything is set on yes except adding records, so it's not that change records is set to no.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As Variant
strSQL = "SELECT tblWerknemers.Id, tblWerknemers.Werknemer, tblWerknemers.Selectie, tblWerknemers.Actief " & _
"FROM tblWerknemers " & _
"WHERE (((tblWerknemers.Actief)= True));"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
Do Until rs.EOF
rs!Selectie = True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
Call Form_frmBeheerDeelnemers.Requery
Lets say I have a table with the following columns:
Chainname
Storenumber
Storename
Revenue in value (sold goods in $)
Revenue in volume (sold goods in pieces or SKUs)
Month
I create an Append query where I use these fields from my table:
Chainname
Storenumber
Storename
Revenue in value (sold goods in $)
Month
What happends afterwards doesn't matter, but I what if I would like to change a field in my Append query to this:
Chainname
Storenumber
Storename
Revenue in volume (sold goods in pieces/SKUs)
Month
I can do that manually by opening the Append query and then change it in the field...But can I change it without actually opening the query. Lets say using a Form were I choose if I wanna use:
Revenue in value (sold goods in $)
or
Revenue in volume (sold goods in pieces/SKUs)
??
I have the following query, named "Query9" (Right, I didn't assign it a name when I first created it). Its SQL is:
SELECT LA.DL_ID, LA.UPB, LA.[FMLNo]
FROM LindActivity AS LA
UNION ALL SELECT [Q].[Deal ID],[Q].[CSUPB],[Q].[CBIID]
FROM [qry_DSRCB] as Q;
Both LindActivity and qry_DSCRB are queries, neither of which makes any reference to "Query9".
This query runs fine. However, when I change its name to PCBStack, it can't find Query9.FMLNo. If I change its name back to "Query9", it runs fine again.
Also, if I copy the SQL from Query9 and create a new query, PCBStack, and paste the SQL into it, it runs fine.
Anyone have any idea what is going on? I am using Office 2003 with Windows XP
Hi there,
I have a warehouse table that lists a stock code with the last 12 periords closing balances in a record.
I would like to write a query that just shows each stock code with only one month per record and has the openning and closing balance for that month.(the closing balance of one period is the openning balance of the next period).
So the Table currently looks like this
StkCode....Whouse....12.....11......10.....09..... .08.....07
1111.........TZ..........100....200....300...400.. ...500...600
I would like it like:
StkCode....Whouse....Period....open....close
1111.........TZ..........12.........200.....100
1111.........TZ..........11.........300.....200
1111.........TZ..........10.........400.....300
1111.........TZ..........09.........500.....400
1111.........TZ..........08.........600.....500
I haven't worked with access for years and can see how to do this in my head - i just can't figure out what to write. Any help would be greatly appreciated.
I had been using some SQL in Access with many IIf statements. I understand the equivalent in SQL is CASE, WHEN, END.
However, I'm really stuck with the following:
oh..ISNULL used to be Nz as well
IIf(ISNULL(ZI.InvoicesRaised,0)=0,0,IIf(IIf(ISNULL (ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalDebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalDebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0),0))
+IIf(ISNULL(ZIMinus1.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0))*30)/ISNULL(ZIMinus1.InvoicesRaised,0))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0))*30)/ISNULL(ZIMinus1.InvoicesRaised,0))),0),0))
+IIf(ISNULL(ZIMinus2.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0))*30)/(ISNULL(ZIMinus2.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0))*30)/(ISNULL(ZIMinus2.InvoicesRaised,0)))),0),0))
+IIf(ISNULL(ZIMinus3.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0))*30)/(ISNULL(ZIMinus3.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0))*30)/(ISNULL(ZIMinus3.InvoicesRaised,0)))),0),0))
+IIf(ISNULL(ZIMinus4.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)-ISNULL(ZIMinus4.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0))*30)/(ISNULL(ZIMinus4.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)-ISNULL(ZIMinus4.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0))*30)/(ISNULL(ZIMinus4.InvoicesRaised,0)))),0),0)) AS DD
Do I have to put in some seriously heavyily nested CASE statements or are there some other ways round this?
G'day, I'm trying to replace a date on a table using an update query. I have the criteria set to [enter date] but it won't put it in the update to block. I know this has to be one of those easy one but after a search and using help I can't nail it down. Much appriciated, Tim
Guess I should add that I'm going to use a command button on a form to run the query and I want it to prompt me for the date.
I would like to know how to display or change the value in crosstab query to text instead of numbers. I understand that the value must be numeric. Is there another method?
I would like to create in the folliwng format....
Hi,
The query for my form has a boolean field with ' like "No" ' in the criteria. I want users to be able to use a command button to change this to ' like "Yes" ' then requery the form.
Basically, what I want to do is toggle between current records ("No" in the boolean) and Archived Records ("Yes" in the boolean).
Any ideas?
Regards,
Is the a way of changing the fields selected in a query by a form.
I have a form showing how many people need to do each module. Each textbox is populuted by a count from the table.
The idea is simply to avoid having 26 queries based on each module. Is there a way to have the field name changed via the form.
I have used the on dbl click event to pass the field name to a hidden field on the record. I just need to pass the data in the hidden field to the select query.
I have a form based on a query, I would like to add additional criteria to the query dependant on the status of a check box. Is this possible if so what would the code look like?
View 3 Replies View Relatedhttp://i.domaindlx.com/wheelofgod/Tripledemo.asp
I need to change the Query somehow. I had earlier made a table consisting of book # , Chapter # , and verse #. I had seen a triple listbox online and asked the designer to make the necessary changes to make it work. So he created 3 Queries, a book Query, a chapter Query, and a verse Query.
But later I decided to remove realizing that some columns were unnecessary, it messed up the chapter Query and verse Query. Can someone guide me how to recreate the Queries?
I'll post a sample for you to check. I reduced the data to make it postable, but it affected the "chapter" Query and the "verse" Query.
What I need to to is delete the "chap" column and the "vers" replacing them with the existing "chapter" column and the "verse" column. I would appreciate if someone can work with me on this one.
Hello, I am trying to change some query critera with a script.
Basically I have a drop down list which I select and once selected it requeries a subform. I would like to change one of my query parameters, specifically the criteria in a column.
To change query "MyQuery" column "Model" criteria, I've tried:
Code:!Queries!MyQuery!Model.Criteria = "Ultra5"
but that doesn't work. Is that even close to being right?
Thanks in advance!