Simple Up-date Querry
Nov 20, 2007
hello all,
i would be extreemly grateful if anyone could offer me some help with this,, basically i have two tables containing the same fields,, one of the table has approx 3k entries where the other has approx 100 entries,, table two (the smaller table) has the same data the only difference being the percentage filed will have increased. for example Tabel 1 has JC111 at 10% and Table 2 has JC111 at 20%,, my life would be made so very much happier if i could find a method of running an up-date query that would take the 'new' % values from table 2 and post them into or create a new table that has the correct/new %'s.
i really hope this makes some sense,, i would appreciate anyones help
best regards
View Replies
ADVERTISEMENT
Oct 25, 2006
I Have A Querry That Adds The Amount Of A Certain Light Bulbs I Have Order Since 2003. I Then Subtract The Amount Of That Light Bulb I Have On Hand Now...it Gives Me The Amount I Have Used Since 2003. Now I Want The Querry To Take That Number And Divide It By The Numberber Of Months Its Been When The Querry Was Open.
Example...i Open The Querry 10-23-06
It Tells Me The 60 Watt Bulb
Has Been Ordered 900 Times Since 12-2003
I Have 300 In Stock On 10-23-06
I Have Used 600 Since 12-2003
The Querry Needs To Give Me This Answer
You Use 17 60 Watt Bulbs A Month
View 6 Replies
View Related
Nov 20, 2007
hello all,
i would very much like anyones help with this,, i'm hoping its not too much to ask,, the story is a sfollows,,, i have two tables both conatining the same fields,, table 1 has approx 1k entries, where table 2 has approx 400 entries,, the data in table 2 is the newer data. what i would like to do is run a querry that would enable me to 'post' the data from table2 into table1,, for example table 1 has activity jc111 at 10% where table2 has jc111 at 20%,, if i could simply run this querry that would generate a new table or just up-date table1 my life would be very much better,, i really appreciate anyones help
- please remember that i'm quite new to this
thank you very very much in advance
best regards
View 3 Replies
View Related
Sep 11, 2003
In my database I have records that are exactly the same except for some dates. My fields consists of the following: “Model Number”, “Serial Number”, “Last Cal Date”, “Cal Due Date” “Days Due” and “Interval”. The Cal Due Date and Days Due are calculated fields in my query. Each time a device has been recalibrated it is logged into the database. Example my torque screwdrivers are to be calibrated every three months. Once the screwdriver has been recalibrated it is logged into the database. The model number stays the same, the serial number stays the same, a new “Last Cal Date” is entered. I select the interval and the “Cal Due Date” and “Days Due” are calculated. Every time calibration is due I repeat this process. So in 1 year I will have 4 records showing that serial number XXXX was calibrated and when. This process is repeated for every piece of test equipment that requires calibration. How can I query to give me the most recent records for the entire database for each unique serial number.
View 3 Replies
View Related
Jan 10, 2008
I'm replying because I am having the same sort of problem with my current database. I do have the separate tables like don has described. Basically here is what I have:
Employee table:
Employee number (primary key)
Other fields for employee information
Training table: (A different table for each training that I am tracking)
ID (autonumber - primary key)
Employee number
Training Date
There is a new entry on the training table each time an employee completes a training. I then have a query that links the 2 tables (using a join between employee number) and calculates next training due dates and filters to overdue training and trainings that are due in the next 30 days. Currently, the query is not pulling in the most recent training dates for each employee. What do I need to do to the query design so that it will only pull in the most recent training date for each employee? Any help would be greatly appreciated.
Thanks.
View 3 Replies
View Related
Oct 3, 2006
Hi all,
Please forgive a newbie that asking the stupid question.... i just wonder is that anyway to set the date format to short date with instead of mm/dd/yyyy to dd/mm/yyyy to let the user to keyin?
Thanks alot.
View 3 Replies
View Related
Jul 5, 2006
I got this to work easily on another form but on this particular form it doesnt work. I enter a command button and name it d1, then I enter this code for it to populate the current date in the field next to the box:
Private Sub d1_Click()
Date_Entered.Value = Date
End Sub
Can anyone see why it doesnt work :eek:
ERROR IS:
Run-time error '2465':
Database cant find the field 'Date' referred to in your expression.
When I enter a date field it enders whats in that field (blank) into the field I told it (Date_Entered) on my form.
View 3 Replies
View Related
Jul 5, 2006
I got this to work easily on another form but on this particular form it doesnt work. I enter a command button and name it d1, then I enter this code for it to populate the current date in the field next to the box:
Private Sub d1_Click()
Date_Entered.Value = Date
End Sub
Can anyone see why it doesnt work :eek:
ERROR IS:
Run-time error '2465':
Database cant find the field 'Date' referred to in your expression.
When I enter a date field it enders whats in that field (blank) into the field I told it (Date_Entered) on my form.
View 2 Replies
View Related
Sep 13, 2006
OK... what am I forgetting?
I have a linked SQL table with a Date/Time field called DATE. ALL I WANT TO DO is pluck all the records with a single date from that table.
Predictibly, the date field has values like this:
9/11/2006 12:40:46 PM
If in the MS-Access query I use criteria of #9/11/2006# I get other dates also. I have tried variations on Date() or DatePart but those either fail or don't work. Sheesh.
ULTIMATELY, I want to restrict records to a specific date, and then a certain hour of that date, so if you can include this bit of additional logic in your response, that'd be great too. Note: I am trying to structure a query, not write code.
Thanks!
View 2 Replies
View Related
Sep 16, 2005
I have the following fields in a query: "Employee_number", "shiftname", and "shiftdate"
And I have the following code on the criteria of "shiftdate": >= dateadd("d",-7,Date()) so that only records from the past 7 days are displayed which is fine, however when I run the query it displays the following
Employee_numbershiftnameshiftdate
1A16/09/2005
2A16/09/2005
3B15/09/2005
4B15/09/2005
which is not quite what i want.....Is it possible to make it only show the same shiftname with the same shiftdate just the once instead of showing it several times for each employee?
View 4 Replies
View Related
Sep 16, 2007
Guys,
Could you please help me to make a query with the below conditions from the attached test.mdb file?
1. Enq Received Date 10 days before the search date AND/OR
2. Technical Sub Date 4 weeks in advance of the search date AND/OR
3. Potential Date 4 weeks in advance of the search date
The querry will display all the records which satisfies the above conditions.
Thanks in Advance.
Prajith
View 11 Replies
View Related
Oct 17, 2006
I have a table, [temp_indicateur] which has fields [id,libelle_court,description,calcule,resitute,orig ine,seuil_min,seuil_max]
[number,text,text,boolean,boolean,boolean,number,nu mber]
My querry is this: insert into temp_indicateur (id,libelle_court,description,origine) values (" & rs!id & ",'" & rs!libelle_court & "','" & rs!description & "'," & rs!origine & ")"
But it gives me an error: Too little parameters
The table has no primary keys and no foreign keys. rs!id is the value of an earlier querry which I am running. Independently, I get the values of rs correctly but I can't get them to execute in this querry.
View 2 Replies
View Related
Jul 19, 2005
My current table
ClassName PropertA
ClassA 1
CassA 2
ClassB 2
ClassA 3
ClassA 2
ClassB 2
THis is my sample table with two columns. Property A can have 3 values 1,2 and 3. I want TO SELECT 4 columns such as classname, CountOf1, CountOf2, CountOf3. I just want ClassA in one row and at the same time get the number of 1's, 2's and 3's for that classA in seperate columns in the same row.
I appreciate your time. Please Let me know how to
write this querry.
View 1 Replies
View Related
Nov 28, 2005
Hello,
This may sound too common a question, but I really need help on it. Prior querries in the same do not seem to solve it.
I have two tables, table1 and table2 with the following fields
table1 ( ACID, name, address, Salary, MStatus,Age)
table2 ( ACID,salary, Age)
ACID is the common field in the two tables ( primary key in table1)
Required
I need to update table1 with the colums in table2 for all the similar ACID in in table1.
Basically, after teh update, table1 will have all the information it had before the update, but a few fields will have changed coz of teh new information coming from table2.
Please help with the code. I'm using access, and the table structures are all the same.
Thanks in advance
View 1 Replies
View Related
Feb 21, 2006
I have these three tables A, B, C and D. IN table A, SSN is the primary key. Tables B, C, and D have other columns, but the also have SSN as one of the columns. Also SSN is NOT required in other tables.
What I need to do is to be able to run an update querry that will update the value of the SSN in table A, and in any other table that will have that SSN.
I use Access 2000
View 1 Replies
View Related
Mar 14, 2008
Hi,
I am just starting with access and dbs and already have a problem
i am working on a db for managings goods being retured
my db has particular tables:
GOOD
GUpc (PK)- unique product code
GDescription
PERSON
PReference (PK)
PName
PSurname
PTel
PMail
COMPANY
CReference (PK)
CName
CAddress
CTown
CPostcode
CCountry
RGA
RGAReference (PK)
CReference (FK)
PReference(FK)
GUpc(FK)
RGADate
RGASatus
RGACostOfRepair
I managed to get a query working so that i can search the PERSON table searched by surname
PARAMETERS [PSurname] Text ( 255 );
SELECT PERSON.PReference, PERSON.PName, PERSON.PSurname, PERSON.PTel, PERSON.PMail
FROM PERSON
WHERE (((PERSON.PSurname)=[PSurname]));
but i cant get it to work i wanted to search it by PReference
PARAMETERS [PReference] Text ( 255 );
SELECT PERSON.[PReference], PERSON.[PName], PERSON.[PSurname], PERSON.[PTel], PERSON.[PMail]
FROM PERSON INNER JOIN RGA ON PERSON.PReference=RGA.PReference
WHERE (((PERSON.PReference)=[PReference]));
I get a msg that The expression is typed incorectly or is to complex to be evaluated. For example a numeric expression my contain too many complecated elements. Try symplyfing the expression by assigning parts if the expression as variables.
AND THIS IS NO HELP FOR ME... francly
The same situation is with all the other tables- its possible (query- search) for everything apart for the primary keys.
I prepared a querry gathering data from all those tables. I managed to get it work with parameter like the one below with surname
PARAMETERS [PSurname] Text ( 255 );
SELECT COMPANY.*, RGA.*, PERSON.PName, PERSON.PSurname, COMPANY.CName, GOOD.GDescription
FROM PERSON INNER JOIN (GOOD INNER JOIN (COMPANY INNER JOIN RGA ON COMPANY.CReference = RGA.CReference) ON GOOD.GUpc = RGA.GUpc) ON PERSON.PReference = RGA.PReference
WHERE (((PERSON.PSurname)=[PSurname]));
but the same situation again, i cant get it to work if i want to get it for RGAReference, CReference, PReference,GUpc
I don't know what i am doing wrong?
have I designed it all wrong or is it just a small thing...
Thank you for all your help
My problems started when I tried to run a querry that will return the GDescription (this tables store data about 3000 products)
View 2 Replies
View Related
May 6, 2005
I have created a search form made up of a number of different boxes. In my querry for the search I am using the following statement:
Like "*" & [Forms]![Search]![ExerciseName] & "*"
I a have a number of theses all saying similar things. At present I have to put something in each of the search boxes. Is there a way that I can leave a box blank and still search by other criteria. If I leave a box blank at present it returns no results.
View 11 Replies
View Related
Sep 20, 2004
Hello All
This might be an easy problem but I am not quite sure how to go about solving it. Presently I have a querry that is linked to a form so that when a person selects a role from the drop down list and you click search it uses the elment selected in the drop down list to show the coreesponding users. My probelme now is that I want to be able to select two different roles and use this querry to give me the users corresponding to these 2 roles. I would really appreciate your help what I have tired to do is create a new combo box and named it role 1 (the first one was named role) and then in the query in the criteria i tired to put the following
[Forms]![Users To Role Search Engine]![Role, Role1]but that give me nothing I am sure there is a flaw in my logic but I am not sure how to solve this. Thank you for your help
View 2 Replies
View Related
Oct 13, 2006
Hi,
I have this querry but when I execute on a button click it dosen't give me any error....but no data is saved in the table, why????
strSQL = " UPDATE DISTINCTROW projet set id='" & txt_id.Value & "',code_NOP='" & txt_code_NOP.Value & "',client='" & txt_client.Value & "',libelle_projet='" & txt_libelle_projet.Value & "',id_clarity='" & txt_id_clarity & "',technologie_projet='" & txt_technologie_projet.Value & "',em='" & txt_em.Value & "',date_debut=#" & txt_date_debut.Value & "#,date_fin=#" & txt_date_fin.Value & "#,site_mandataire='" & txt_site_mandataire.Value & "',charge_totale=" & txt_charge_totale & ",ca_total_vendu=" & txt_ca_total_vendu.Value & " WHERE id='" & Form_projet.id.Value & "';"
View 7 Replies
View Related
Dec 1, 2006
I have a string which I want to use as a querry, but its giving me an error saying "object required" even before executing the querry..
strSQL = "INSERT INTO projet (id,code_NOP,client,libelle_projet,id_clarity,tech nologie_projet,em,date_debut,date_fin,site_mandata ire,charge_totale,ca_total_vendu,no_sous_fon,no_ec rans_s,no_ecrans_m,no_ecrans_c,no_traite_s,no_trai te_m,no_traite_c,no_impression_s,no_impression_m,n o_impression_c,prov_risque,prov_garantie)" & _
" values('" & txt_id.Value & "','" & txt_code_NOP.Value & "','" & txt_client.Value & "','" & txt_libelle_projet.Value & "','" & txt_id_clarity.Value & "','" & txt_technologie_projet.Value & "','" & txt_em.Value & "',#" & Format(txt_date_debut.Value, "mm/dd/yyyy") & "#,#" & Format(txt_date_fin.Value, "mm/dd/yyyy") & "#,'" & txt_site_mandataire.Value & "'," & txt_charge_totale.Value & "," & txt_ca_total_vendu.Value & "," & txt_sous_fon.Value & "," & txt_ecrans_s.Value & "," & txt_ecrans_m.Value & "," & txt_ecrans_c.Value & "," & txt_traite_s.Value & "," & txt_traite_m.Value & "," & txt_traite_c.Value & "," & txt_impression_s.Value & "," & txt_impression_m.Value & "," & txt_impression_c.Value & "," & txt_prov_risques.Value & "," & txt_prov_grantie.Value & " );"
Can anyone solve it?
View 1 Replies
View Related
Dec 6, 2006
Hi,
I am running a querry, but it is not selecting any data...
Now I have checked all the stuff..everything is fine except the date part.
strSQL = "select periodicite.libelle as pl,indicateur.libelle_court as il,valeur.valeur,valeur.date_valeur,valeur.id_proj et" & _
" FROM valeur,indicateur,periode,periodicite" & _
" WHERE valeur.id_indicateur=indicateur.id " & _
" AND periodicite.id= " & cmb_periodicite.Value & "" & _
" AND periodicite.id=periode.id_periodicite" & _
" AND periode.id=valeur.id_periode" & _
" AND valeur.date_valeur BETWEEN #" & txt_date_debut.Value & "# AND #" & txt_date_fin.Value & "# "
I am printing the querry too, so that I can check whether it is giving the correct values from the txt_date_debut and txt_date_fin. I found out that everything is fine. I just can't figure out the problem !!! Can anyone help please????
View 2 Replies
View Related
Oct 1, 2005
Hi There
I Need A Querry For Commission Sales.
I Have 10 Items 7 Items At 2% Of Commission 3 At 4%. Each Salesman Have To reach A Min Of 5000 USD Of Sales Per Month Then All Sales Above That Amount Commission Will Be Calculated.
I Need A Querry To Calculate Sales And If Salesman reached The Min Sales Commission Is Calculated .
Any Help ???????
Thanks
View 1 Replies
View Related
Oct 18, 2005
I have a table in access. The columns are State, FICO, LTV, DTO,LBI and Grade need to update the column (Grade) so that it gets the values A, B, C or D based on the following conditions
If FICO >525 and LTV <100 AND DTI <50 then grade = D
IF FICO >600 AND LTV BTWN 90 -100 AND DTI <50 Grade =c
If FICO >600 AND LTV <90 AND DTI<45 Grade =B
IF FICO >680 AND LTV <90 AND DTI<45 grade =A
IF FICO >720 AND LTV <100 AND DTI<50 Grade =A
Please help me figure out the ACCESS select querry for this.
View 2 Replies
View Related
Mar 19, 2006
I have a table with the following fields:
Record_No <--- This is an autonumber field
Est_COE --Date Field Type
DATE_DOCR
DATE_Followup
Progress
I then have used multiple querries that just count records based off of Status:
Example of one of the querry's:
SELECT Count(borrower.Record_Number) AS Close_Of_Escrow
FROM borrower
WHERE (((borrower.Est_COE) Between Date() And (Date()+7)) AND ((borrower.Progress)="active"));
Another Example of one other querry:
SELECT Count(Borrower_Journal_Notes.ID) AS Followups_Not_Done
FROM Borrower_Journal_Notes
WHERE (((Borrower_Journal_Notes.Follow_Up_date)>Date()) AND ((Borrower_Journal_Notes.Followed_Up_Complete) Not Like -1));
Then I put both these querry's into a another querry so i can get the data put into a single form using one record source. (The master querry)
View 3 Replies
View Related
Jul 30, 2007
Hi Guys,
can anybody help me on the below?
i need to generate a query contains
name, project, total_Hours_worked (only if total is >45) for a week by each employees. the query should contain total hours for all the weeks.
The table contains week_ID column and dyas worked column.
i can create a query which gives me sum of day_hours worked using selecct sum(day_hours) as Total.
but i badly need total sum of week hours worked by each employees. so that i can generate overtime report grouped on weekly who has woked more than 45 hours per week.
the table is attached. please see and help me if you can.
Thanks
Kiran Konsaai
View 2 Replies
View Related
Aug 13, 2007
Hi,
I have a paramter querry, which asks for "Name" If I want to open it.
Now If I open, It asks "Enter parameter value"
I want it should change text As " Enter Name"
Secondly I want a drop down list in enter name text box , Instead of typing name , I would like to choose one from the list.
is it possible , If yes Please Help.
Best Regards
View 6 Replies
View Related
