Hi There
we have a ms access application which pools data from AS400.
we have scheduled it for every week but there is a situation when its missing the data to feed the tables.
but when we run the queries manually it gives the desired results.
all kind of response are welcome
Best
danny
I have built a query that should append to another table. Please see SQL below -
INSERT INTO tblMyBackupContent ( LoginName, Title, Type, Code, [Date Assigned], [Date Started], [Last Accessed], Progress, [Date Completed], [Time Spent (min)], Score, Result )
SELECT tblConentImport.LoginName, tblConentImport.Title, tblConentImport.Type, tblConentImport.Code, tblConentImport.[Date Assigned], tblConentImport.[Date Started], tblConentImport.[Last Accessed], tblConentImport.Progress, tblConentImport.[Date Completed], tblConentImport.[Time Spent (min)], tblConentImport.Score, tblConentImport.Result
FROM tblConentImport
WHERE (((tblConentImport.LoginName)="iderand"));
When I run this query, it shows that 41 records are going to be appended. I click on yes, but when I go into the table to view the appended records they do not show.
Please help me.
:o Okay. I have a frustrating one. This is embarassing to me, because it seems so easy. Anyways, I have a form, where a person could select up to 3 different months and up to 3 different paper types. These combo boxes are listed in the criteria of those fields in my query. If I use the form and try to run my query, it gives me blank results. If I run the query and fill in the pop ups that show up asking for the information that the form is referring to, and I type in the same information, I get the results I expected. What am I missing here? Please help before I go bald!!!
View 8 Replies View RelatedHi Folks,
I have answered my own question so I thought I would share as I couldn't find the solution in any posts. I confess that I don't understand why my results were wrong, but I managed to get them right. :rolleyes:
When using Dcount in a query, I was getting results which did not match the query results. For instance:
Phase_2: DCount("Project_Phase_ID","tbl_Prj_Details","Project_Phase_ID = 2")
gave an answer of 27 when there were in fact 41 projects in that phase.
Searching the forum I came accross this:
Count() always counts the entire domain.
So, Dcount is not counting the record set of my query but something else. I have 4 tables in the query and no idea what domain my dcount was looking at. I presume the various join types were messing with it somehow. :confused:
To get round this, I stripped out the Dcount expressions and changed the query to a make table. I then used the created table as the basis of a query in which I had my Dcounts. The dcount results now agree with the query recordset. :) :) :)
Any background on the bits I clearly don't understand will be gratefully received. I hope this helps someone else sometime.
Kind regards,
Keith.
I have a query that selects from a table base on 3 entries (Name, BeginDate and EndDate) and should show me 12 other columns and their entries... I have 9 entries for a particular Name, but when i run the query i get 15 results. Some are duplicated but others are not and I don't know where to begin narrrowing it down. Any ideas where to start?
View 2 Replies View RelatedHi ,
I am using Access Application with Orcale Db as backend and this application is already in production.
The problem is.. USers Complained that the system is slow, this is because there is refresh (requery) for each Insert (When they click submit, that particular record is inserted and then a refresh has to be done to bring only unused member IDs on the User screen), i had used a Sql query using NOT IN , then i realized and now changed it to NOT EXISTS, but with this new query , if i test recordset. EOF , even though the query returns more than 13000 ..records, EOF sets to TRUE and the other part (not supposed to) gets executed. I really cant figure out why, i took the same query put it in query builder in Access and ran it , it gave 13000 records..but in VBA , this recordset is not giving expected results and so..i am totally confused..The part of the Code is shown below.
Code:newquery = " SELECT A.MEMBER_ID, A.MEMBER_NAME, A.ADDRESS_LINE1.................. FROM TABLE A WHERE NOT Exists ( SELECT '' FROM TABLE B where A.MEMBER_ID =B.MEMBER_ID)AND A.MATCH_LEVEL <> 0 ORDER BY A.MATCH_LEVEL DESC"rst1.Open newquery, Cnt, adOpenDynamic, adLockOptimisticIf rst1.EOF = False ThenForm_PHS_ASSIGN.RequeryForm_PHS_ASSIGN_SUB.RequeryElse MsgBox " No records to Process!" End If
EVEn though it should requery , it gives a message No records to process which is wrong. It works with NOT IN.... BUT VERY SLOW, NOT EXISTS IS FAST BUT DOES NOT GIVE ME WHAT I WANT..
Where am i going wrong..help pls!!!!
THANKS VERY MUCH!!
I am finishing up a database that logs basic activities/tasks for my coworkers. As they go in to the database and log an activity in it stays open until they end their day or they start a new task. All this works fine, but the problem comes when I try to determine the total time spent (in hours) on each activity/task. I have written similar queries before and they worked just fine, but for some reason I am having trouble getting this one to work. Here is the expression I'm using...
Time Diff (in hrs): DateDiff("h",[Sample_TM_Table_1]![Time_In],[Sample_TM_Table_1]![Time_Out])
Unfortunately, say Time_In = 8:32:38 am and Time_Out = 8:33:03 am
The expression says that is equal to 946632.
For another Time_In = 8:33:00 am and Time_Out = 3:18:19 PM.
The expression says this equals 946639.
I have tried changing the format of the date/time in both Time_In and Time_Out fields to ensure they were the same and cross checked the expression but still get the same answers each time. Even when I try to use "n" for the interval or "s" it is still highly incorrect. The only thing that makes me think I still have a formatting issue is that the Time_In shows a long date on the results table while Time_Out shows it in a general date format. However, when I go into both the table and the queries to check the formatting they both show general date. Any ideas? I'm all out! Thanks in advance.
Hi
I have set up a form to run a query with the criteria linked to the options/drop down menus on the form. I am able to search for criteria set out in the combo boxes. However, what i would like to ensure is that if the drop down boxes are left blank the results will be to show 'all' results for that field.
help is greatly appreciated!
thanks in Advance!
Shapman
I have a DB and I have a button that triggers the find and replace. this will search over 100K entries and filter a specific client. The clients record can be 100's of entries.
This all works fine, but its always ascending and I have to scroll to the bottom for the latest record.
I can't set it to show Descending permanently as some employees can't cope with change. so...
I have a checkbox on my form for either Ascending or descending results. Is there any way I can get the find / replace to reference it, Or can I re-sort them after the search.
I've tried putting re-sort code on the 'find' button as lostfocus and mouseup but it doesn't work.
it is possible to Open access application like desk application done with java or vb, or install access application as exe or similar methods. My requirements is to giving a access application to customer with data base for printing an invoice. when he running the aplication he can see access open and tables, queries forms left hand. how to hide these thing ?
View 4 Replies View RelatedHow I can save a Query result with a desired name. I use the following simple code.
Code:
SELECT * INTO Table
FROM Query;
Now the results will be saved in "Table" but I want acces to ask me for what to call the table when the query is runned. Is that possible with a query?
I have a table called tblObligMstr. I have a form called frmAmendRecord that is based (bound) on tblObligMstr - three of the fields are ID, IntControlNr, and Amend.
There is also a subform named tblObligMstrSubform. The Link Master and Link Child for the subform fields is IntControlNr. So the subform shows all of the records that have a specific IntControlNr. The form shows the data associated with the first record with a specific IntControlNr
Here is the problem -
The purpose of the form is to show the data for the last record (not the first) in the subform list of records. Certain data on the form can be edited, and then the edited form needs to be saved as a new record in tblObligMstr.
Data in table “tblEmployees”:
EmpName GroupNum
Jon Group1
Sam Group2
Tom Group1
Bob Group1
Hal Group2
Dan Group3
Cal Group2
With sample table above, can I write a query in Access that lists the data by GroupNum based on my criteria, say Group2 1st, then Group3, then Group1? Basically, how do I make the result show:
Sam Group2
Hal Group2
Cal Group2
Dan Group3
Jon Group1
Tom Group1
Bob Group1
I'm building a query using wildcard "like". See the attached file. My question is that why doesn't the query "result" return "aaaaa" for "aaa" is contained within "aaaaa"?I'm basically limiting my records to those found in tbl_site based on a wildcard "like".
View 12 Replies View RelatedWe have an extensive reporting application in Access and would like it to be available through a web application. I've went down the road of migrating this application to SQL to use SQL Reporting Services; this is a good tool but I am wondering if MS Access has an easier solution for me other than migration to SQL.
I'm looking for a solution that will make these reports available over the web for many users. Price tag is a consideration...need to know if there is any.
Please help...thanks in advance for any thoughts!!!!
My database is for Records Management of HSE Files. There are tables built for each of the seperate pieces of information (example: Categories contains a code column and a description column (101 - Air Management). The Data Entry form for the Files Table requires the entry of the Category code and the Category description in order to populate the Files Table. The Labels Report pulls from the Files Table, and if both the Category code and Category description are not populated in the Files Table (which is populated from the Data Entry Form) then the Labels Report does not produce the correct information. Plus, the Catagory code column and the Category description column both have to have the code in order to have the Labels Report produce the Category description (and not the code).
As well, in my Data Entry Form I would like to only enter in '101' and have 'Air Management' automagically populate (I've done it once - but it doesn't replicate through the Database to the Files Table! And then I have to input the data directly into the Files Table to make the Labels Report produce the labels. argh!).
I am wondering - do I even need the two columns in the Files Table (Category code and Category description) or should I just have the one (Category code) and somehow build the Labels report to replace the Category code with the Category description - and would that have something to do with the bound columns?
:confused:
I've been pulling out my hair on this for a while, but as I have more and more files to enter into the database, I'm not so intersted in entering in data twice!
Thank you for any assistance you can provide.
I created a subform that displays the records of a select query after selecting the desired data from a single combo box (for instructors name) and then using an OpenQuery macro attached to a button.
Only 1 record is displayed in the subform each time I hit the button. If I look at the query return there is no change. However, if I close the query and select a new instructors name, although I only get one record in the subform, the query when opened shows all the correct records.
What is wrong with my macro/subform?
A2010 - on an existing form I have a few controls that I want to hightlight. I thought I would create a rectangle covering only the desired controls and then make the back colour a light grey. But if I do then I cannot see the controls. If I change the back style to transparent then the colour reverts to nothing and I'm back to square one.
It feels like I want to bring the existing controls to the font - but how?If I create new controls on an existing grey rectangle then it works fine.
Hello everyone,
Problem: I have an MS Access database in Frontpage that I'm using for my online site. Table is set up w/ a field named, "Artist" (amongst others). In this field I have artist's last name followed by a comma, a space, then the artist's first name. I am using the CONTAINS operator for my sql query statment. Everything works fine on the site if you search only by the first name, last name, or by typing in, exactly, the artist last name, comma, space, then the first name i.e. Presley, Elvis. However, if one were to type in "Elvis Presley", no records return. Why is this? I have also tried the LIKE operator and I get the same results. Here is the code where 45s is my table name and the others are just additional fields of which i have no problems:
SELECT * FROM 45s WHERE (Artist LIKE '%::Artist::%' OR Title LIKE '%::Title::%' OR Record_Label LIKE '%::Record_Label::%') ORDER BY Artist ASC
I'd appreciate any help as I've failed to get a solution from other forums and computer scientists that I've asked.
Thanks!
David
This query is getting complicated for me... I need help please! :(
I need it to give me the data for people between FirstMonth, Year and SecondMonth, Year...
Right now it's giving me only the FirstMonth, Year and SecondMonth, Year...
I think I need a between there somewhere but not sure where to put it...??
Hope this makes sense.
I'm doing the query in Access 2002
Thanks
Sarah
SELECT [FirstName] & ", " & [LastName] AS FullName, TriOct10.FirstName, TriOct10.LastName, TriOct10.Address, TriOct10.City, TriOct10.Prov, TriOct10.PostalCode, TriOct10.VolScreenCode, DatePart("m",[PRCDate]) AS Month2, DatePart("yyyy",[PRCDate])+3 AS PRCDueY2, TriOct10.PRCDate, TriOct10.MemberType, TriOct10.MemberStatus, TriOct10.ExpandName, TriOct10.RegOrgName, TriOct10.RegisteredRole
FROM TriOct10
WHERE (((DatePart("m",[PRCDate]))=[Enter 1st Month]) AND ((DatePart("yyyy",[PRCDate])+3)=[Enter First year])) OR (((DatePart("m",[PRCDate]))=[Enter Last Month]) AND ((DatePart("yyyy",[PRCDate])+3)=[Enter Last Year]) AND ((TriOct10.MemberType)="volunteer") AND ((TriOct10.MemberStatus)="Active" Or (TriOct10.MemberStatus)="probationary") AND ((TriOct10.ExpandName) Like "*" & [What Area?] & "*"))
ORDER BY DatePart("m",[PRCDate]), DatePart("yyyy",[PRCDate])+3;
I have a query that uses "<>" to find records that are not equal between 2 tables. The formula looks like this:
<> [tblWorkPerformed]![ID]
It works fine as long as there is only one record in tblWorkPerformed, but once I add a second record I get the records I dont want plus the records I do. The records I do what are there twice. I found how to only get single records of each, but it still desplays the records it should not. I tried a dummy database, and it does the same thing. Do I have something wrong or is this just not possible.
Thanks
Anthony
I'm trying to construct a database to track church funds. I posted before and was given a link to a suitable schema but it was too comnplicated for me! Basically I need to track money given to a church through weekly transactions - money is given weekly through a numbered envelope. Each envelope has a number corresponding to the giver. I came up with this:
tb_parishioners
parishioner_id (PK), fname, lname, address etc
tb_FWO_Nos (FWO = free will offering)
FWO_ID (PK), fwo_Number
tb_contributions
Contributions_ID (PK), parishioner_ID (Both PK), week_no, amount, date
This doesn't really work. One giver can have only one FWO envelope but each envelope has many weeks/dates and I'm not sure how to link this. I want the form to have the week no and corresponding date already there so I just enter the amount. Then it has to be able to switch to a different year. Hope this is clear? Any suggestions?
Thanks.
SELECT Conversion.[Asset Class], iif([asset class]<4000, "1", "2")
FROM Conversion;
Hi all.
I have a sub query that was working until a couple of days ago when it decided to stop.
I have a Table called TblFieldValues which whenever a new value is entered into my relational Db.
Each new Value is given a ValueID and is Dated (full date/time stamp)
My Sub Query ensures that for each FieldID (ie if more than 1) it selects/shows the top most Date....But it is not grabbing all the fields for some reason???
Here is the SQL limiting criteria to a bare minimum (QuoteID)
SELECT Main.FieldID, Main.QuoteID, Main.QuoteTypeID, Main.SubSection, Main.FieldValue, Main.NumberFieldValue, Main.CalcFieldValue, Main.Date, Main.ValueID
FROM TblFieldValue AS Main
WHERE (((Main.QuoteID)=[Forms]![FrmQuote]![QuoteID]) AND ((Main.ValueID) In (SELECT TOP 1 Sub.ValueID FROM TblFieldValue AS Sub WHERE Sub.FieldID=Main.FieldID ORDER BY Sub.Date DESC)));
I had a thought it might be the way the info is put into TblFieldValues, as it is often put in via Code, in fact only when it is put via code is it not showing up as a rule.
So I had a look at the code that inserts it.
strSQL = "INSERT INTO TblFieldValue ([QuoteID], [QuoteTypeID], [SectionID], [FieldID], [FieldValue], [Date], [UpdatedBy]) Values ('" & strQuoteID & "', '" & strQuoteType & "', '" & strQuoteSection & "', '" & strFieldID & "', '" & strValue & "', '" & strDate & "', '" & strUser & "');"
'MsgBox strSQL
DoCmd.RunSQL strSQL
I had Now() in replace of strDate, but tried changing to strDate and diming strDate as Date and then setting strDate = Now() but doesn't really change it in the table.
I am certain it is in this somehow? Any ideas????
Your help will be greatly appreciated...
Hi all,
Would appreciate help on this. I have a query which combines the results of 3 queries. Once the underlying queries each has a result I get a result in my query, but if 1 of the underlying queries has no result I get nothing. (I hope this makes sense).
How can I set my query to show results even if the underlying query doesn't.
thanks in advance.
rgs
Ginny
So I have this relatively simple problem: I need to create a button that once clicked will open the Save As dialog box and allow the user to save a copy of the current database where he wishes. The filename should contain todays date in DDMM format along with some pre-set text e.g. DDMM PresetText.
I am using Access 2010.