Erratic Query Behaviour?
Jun 15, 2007
Hi. I have a question prompted by database behaviour that I do not understand.
I have two tables with related records, one being a subset of the other. I have joined the tables in a query with a Left outer join relationship. I have put the query results in a form, and when a User clicks a Yes/No box, a new record is created in the subset table automatically generating an ID number using an Autonumber set to start at chosen integers.
However, recently error has occured. When I view the query results some records will not allow me to create a record in the subset table stating "You can not add or change a record because a related record is required in table x", eventhough that very record is the record which the query is displaying. Some records will. The behaviour is erratic and I cannot see what is causing it. Might this be indexing related?
Any help appreciated as ever
Matt
View Replies
ADVERTISEMENT
Jun 15, 2006
This is for information only - I am not seeking a solution.
I created a Select Query selecting only two memo fields from a single table.
The query works correctly unless you select Unique Values = Yes which results in the error message "The field is too small to accept the amount of data you attempted to add. Try inserting less data."
View 1 Replies
View Related
Jan 14, 2008
I have a table tbl_Fixtures made up of:
fixture_id
team1_id
team2_id
...
...
...
A team id can appear in either team1 or team2 (depends on how the algorithm to compute who plays who calculates it).
I want to know how many games a team plays, so I wrote 4 queries:
qryTeam1Games:
SELECT tbl_Fixtures.team1_id AS team_id, Count(tbl_Fixtures.fixture_id) AS games
FROM tbl_Fixtures
GROUP BY tbl_Fixtures.team1_id;
qryTeams2Games:
SELECT tbl_Fixtures.team2_id AS team_id, Count(tbl_Fixtures.fixture_id) AS games
FROM tbl_Fixtures
GROUP BY tbl_Fixtures.team1_id;
qryAllGames:
SELECT *
FROM qryTeam1Games
UNION
SELECT *
FROM qryTeam2games;
qryNumGamesByTeam:
SELECT qryAllGames.team_id, Sum(qryAllGames.games) AS SumOfgames
FROM qryAllGames
GROUP BY qryAllGames.team_id;
Unfortunately, if the same team_id and games combination appears in both qryTeams1Games and qryTeams2Games, it doesn't bring 2 records into qryAllGames and the summed number of games is only half of what it should be.
Is this a case where a union query shouldn't be used? Is that how it is supposed to work?
A work around isn't a problem but this did have me stumped for a bit.
View 2 Replies
View Related
Oct 31, 2006
Hello, Something weird happened to me - I have a form with continuous subforms and on it are checkboxes. When I click on one specific checkbox, a text box on the parent form becomes visible, where I can add text, which is added directly to the table in the AfterUpdate event. However, the query underlying the subform does not reflect the change, and when I either go to a different record on the parent form, or click on another checkbox in the subform, I get an error message:"This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. [...]"I can then Save The Record, Copy To Clipboard or Drop Changes.When I drop changes, the text shows up in the query, although my textbox disappears.The one time I selected Save, however, the table got messed up and a foreign key was replaced by what looks like random number [used to be "1", is now "4587525"]. A second foreign key was also changed.Can anybody explain this?And, more importantly, suggest a way to avoid this entire situation? Thank you.
View 1 Replies
View Related
Mar 30, 2007
Hi All
I have just noticed some strange behaviour in access. I hava and asp page which gets two dates which are then used in a sql statement to refresh a page. The end of the statement deals with a between clause like this
WHERE tbl_Movements.date_of_Movement Between #12/03/2007# AND #30/03/2007#
But when I view this in design mode it changes it to
Between #03/12/2007# And #30/03/2007#
Why does it do this and what can I do to resolve it
Thanks
View 1 Replies
View Related
Jan 17, 2008
Hi,
i have a form which is acting strange. i press a button that runs a macro and opens a second form based on my PersonID. ( opens the form showing that person only ). it works great for the first 2 table records. after this, i get a blank screen. not even any text boxes. i have a "where" condition which reads on the following macro sequence:-
OpenForm ( MakeYourPayment )
View ( Form )
Filter Name ( )
Where condition (="[PersonID]="PersonID] )
Data Mode ( Edit )
Window ( Normal )
my second form has PersonID field available so as i read it, my where condition should open the form using Person ID from min form to PersonID on the 2nd form.
any help greatly appreciated as there is a lot of work on this form.
many thank,
Nigel
View 1 Replies
View Related
Mar 11, 2008
Hi,
slightly related to a previous thread of mine. thanks to boblarson and co, i have created a subform on my main form that is blank. then i press a button and the sourceobject is set to the desired form i want to have visible. i have the following code-
Private sub Button_Click()
me![MainFormSubForm].sourceObject = " Edit Person" ' name of form
me![MainFormSubForm].LinkMasterFields = "PersonID" 'field on main form to link from
me![MainFormSubForm].LinkChildFlields = "PersonID" ' field on subform to link to
i can get 4 out of 5 forms to link correctly. i have a listbox on my main form that changes the mainform data to show a different person.with the other forms when they are visible, i can click a different person in the listbox and all info changes. form number 5 "Edit Person" doesnt work. when the form is loaded via the above method, it shows 1 record which is the first in the query that the main form is sourced to. when i click another person in the listbox, the subform goes to a (new) record and does not select the person clicked. the main form changes but not the subform.
why does it do this on one form when 4 others work with the code? the same code is method is used.
regs,
Nigel
View 1 Replies
View Related
Dec 14, 2007
This outputs smth
SELECT Proj_Name & '-' & Proj_ID as Emp_Pr from Emp_Prj where trim(Proj_Name) like trim('Dot')
------
But not this...why ??? I am using ms access
SELECT Proj_Name & '-' & Proj_ID as Emp_Pr from Emp_Prj where trim(Proj_Name) like trim('%Dot%')
View 2 Replies
View Related
Apr 6, 2007
After working great for 12 months my db has suddenly stopped working tonight! after opening on to the first form and clicking off any field I get "Invalid Data Format" error message pop up and transfered to the code windows. Access then tells me it does not recognise the code and shuts down !!! Has anyone got any ideas PLEASE, this is a disaster
View 14 Replies
View Related
Aug 21, 2007
i have an Address textbox that has key behaviour as - new line in field. this allows me to put the complete address in one box, post code is another textbox. this is fine if i want to create window envelopes but - if i want the address to look as one long line in a report it wont work
since the address in the form takes up two lines -- only the first line shows in the address column of the table - hence only one row shows in the report that derives from that table SO =[Address]&" "&[PostCode] placed in the Source Code of the address only shows the first line plus post code . if i make the box deeper then it will show - but only as a second line - i want it all to be one line
can i get out of this and still keep the -new line in field- behaviour?
thanks in anticipation
View 3 Replies
View Related
Aug 22, 2006
Hi,
I am using a form with 3 combo boxes name, weight and grade. The combo boxes are cascading:
A name is selected in the first combo box, since each name has different weights attached to it only the weights associated with that name are selected when the weight combo box is clicked. In a similar vein, there are only certian grades associated with a certain weight.
The effect is to filter the results so that an invalid entry cannot occur.
My problem is that once an entry is made and I select a new name for the next entry, the weight and grade boxes remain with the same values instead of updating to new values. Updating of the boxes does not appear to be working.
Does anyone have any ideas how to solve this problem?
Many Thanks
Turbojohn
View 2 Replies
View Related
Dec 24, 2004
Why does my sub form which is contained in a Tab, not display unless a certain field is populated with something? It's not a required field. Infact it is one of two identical fields... a relationship and a lookup to another table. It doesn't matter if the second one if filled in or not. Just this one. The record is in the database, I choose from a drop down in the header... and the subform then requeries and displays the information. However, unless this 1 field is filled in.. the subform remains blank. It is getting on my nerves!!
View 1 Replies
View Related
Aug 24, 2006
Hello,
I am hoping someone might be so kind as to help me with a search form I am working on. I have included a test database that has the form. The form has a search box that works dynamically to show results based upon a string search as typed into the search box. The problem is: if I type any spaces beyond the last digit of what I am searching for it no longer displays the intended result.
For example; there is a field called - serial - in the test databse you will see a value in that field of 319-04. If I type 319-04 it shows that record only. However if I key in any additional spaces after the 319-04 it will not show the result.
This is just a problem because I am trying to input the data using a scanner. I have found that if I scan the barcode with the serial# it seems to include two spaces after the scan. I don't know why that is...but rather than worry about that I would just like to try and get the form to display the record even if there are spaces after the last digit entered.
Thanks!
View 3 Replies
View Related
Jan 21, 2006
Ok here is the deal
You start to type with a smile on your face
Form_MyForm. <-- from that point (literaly) you usualy have a list of things you can do with that form.
All i wanted to do is a requery but i have no list...and even if i type myself Form_MyForm.requery .. this will not work cause it says that it cant find the dang form.
I have tried renaming the form, let someone else type the name for me, copy&paste ..nothing work, when i get to the point...it never gives me a list of things i can do with that form.
what is the deal here..anyone ?
Wow that felt good only to talk about it
View 3 Replies
View Related
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Mar 4, 2015
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
View 1 Replies
View Related
Oct 28, 2005
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
View 9 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
Jul 15, 2014
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
May 31, 2006
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
View 1 Replies
View Related
Mar 8, 2005
I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.
That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.
I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.
What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.
Can someone see where I’m going wrong?
Slaughter
slaughter at mizzou dot edu
View 9 Replies
View Related
Oct 8, 2013
I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15
View 2 Replies
View Related
