Mapping? Change Query? VBA?

Jan 18, 2008

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...

View Replies


ADVERTISEMENT

Mapping Tables

Jan 2, 2005

hi,
I have two tables named "Uni" and "MainTable"

"Uni" has two fields, named "A" and "B".
"MainTable" has three fields, named "aa", "bb" and FinalAns"

Uni
A | B
-------------
001 | 444
004 | 999
042 | 129


MainTable
aa |bb |FinalAns
--------------------------------
001 |XX|? - should be 444
042 |XX |? - should be 129


Below queries code is written by me:

UPDATE MainTable set [MainTable].[FinalAns] = [Uni].[B]
WHERE [MainTable].[aa]=[Uni].[A]

But it does not works, it prompted me "Enter Parameter Value Uni.B" and then "Enter Parameter Value Uni.A"

Can anyone teach me how to solve my problem? - can be using macro or vb code or queries.

View 2 Replies View Related

Mapping Of Data From Various Columns

Jun 1, 2006

I have an employee with an NI Code of A

NI Contribution thresholds are in the format:

A1a A1b A1c A1d

1000 100 10.00 50.00

However there are many NI Codes and many columns of thresholds for each Code. I am trying to write a query to say:

If NI Code = x then give me values from columns x1a x1b x1c x1d etc

Can anyone help?

Thanks

View 5 Replies View Related

Form Mapping Newbie

Jul 23, 2005

I have designed the database i wanted but now i want to link the fields from a access form to a pre designed template i designed in both microsoft word and pdf. When a user types the information in and clicks preview the information typed in access field will match the field in the template any ideas or software programs that do this.

View 2 Replies View Related

Mapping Week Numbers And Dates

Mar 27, 2007

Hello thank you for reading my question

I am setting up a database for a training centre, part of this includes registration
The academic year I am working with runs over 36 weeks with 24 teaching weeks, 2 x 3 week holidays, 2 x 1 week reading weeks and the final 2 weeks for exams.

I aim to set the system up so that when a user opens the application, I get the username from windows, the day and time then open the correct register. That bit should be fine.

As I have 36 weeks I want to some how map the week numbers to the dates at the start of the term and also the holidays. There are 2 reasons for this, the first being so that when the user logs in the register only shows the current week instead of needing to search along all 24 or 36 entries, the other reason is so that when checking the whole register the holidays are not shown.

Does anybody have any suggestions or pointers as to how I can do this?

Thanks

View 2 Replies View Related

Back End Database Linking And Drive Mapping

Feb 25, 2013

I have built a Database links to multiple back end databases. The whole database is completed, and I have to share it across the company, so I have placed the Database and all of the Back End databases on a "public" shared drive. I have the "public" drive stored as my Q drive. So when I link to the back end databases, the path it stores is Q:DatabasesDatabase.accdb

However, the problem is that not everyone will have the "public" drive mapped to Q. It's possible they could have it mapped to P or A or whatever. So if someone has their "public" drive mapped to P, this database will not work (since Q:DatabasesDatabase.accdb would not exist for them - it's really P:DatabasesDatabase.accdb).

Is there a way around this? So that instead of it mapping to the relative letter (Q or P), can it just refer to "public"?

View 2 Replies View Related

Change Query

Jun 21, 2005

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 Related

How To Change Row In Query

Jul 20, 2007

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

View 2 Replies View Related

Change Value From One Query To Another.

Mar 20, 2005

I'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!

View 1 Replies View Related

Forms :: Change Button Color After Data Change

Apr 5, 2014

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)

View 1 Replies View Related

Change Query In Subform

May 3, 2007

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.

View 2 Replies View Related

How To Change The Criteria Of A Query From The VBA?

Aug 9, 2005

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! :(

View 8 Replies View Related

Change Query Criteria Value?

Jan 18, 2006

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:

View 2 Replies View Related

Trying To Get A Query To Change Text

Mar 26, 2007

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

View 3 Replies View Related

Change Check Box Value Query

Jul 18, 2007

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!

View 4 Replies View Related

Field Change Query

Apr 16, 2008

All,

Is there a way to create a query to show when text in a specific field was changed?

View 2 Replies View Related

Access Query - Change Of Server

Jul 19, 2005

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

View 2 Replies View Related

Sudden Change In Query Performance

Jul 17, 2007

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

View 3 Replies View Related

Query Change Data In Table

Jun 14, 2007

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 Related

Read Only, Can't Change Records In Query

Jul 26, 2005

Ok, 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

View 6 Replies View Related

Change Field Facts In Query?

Oct 20, 2005

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)

??

View 3 Replies View Related

Name Change Prevents Query From Running

Nov 13, 2006

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

View 1 Replies View Related

Query To Change Data Layout

Feb 22, 2007

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.

View 3 Replies View Related

Change Complex Query From IIf Statements

Mar 16, 2007

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?

View 8 Replies View Related

Update Query To Change Date

Apr 19, 2007

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.

View 6 Replies View Related

Crosstab Query Change Value To Text

Jan 31, 2008

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....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved