I have these two queries with the following record count:
qryAzoogleGids has 188 records
qryCJGids has 202 records
qryAzoogleCJUnion displays 294 records and is a Union of the above two queries. So something is amiss! I am short on records, but why?
The database is attached.
Thanks,
Dave
Hi all
This is an ongoing problem I have had for 4 weeks now.
I have made a a system thats acts like a clock In/clock out Out system.
the structure is somthing like this
ID
Username
tblDailyLog
TimeIn
MorningBreakOut
MorningBreakOut
LunchOut
LunchIn
AfternoonOut
AfternoonIn
TimeOut
All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)
When a user arrives in the morning they make a record which they use for the day
They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.
Everything was going fine until people noticed that every now and again a sign in time dissapeared.
I have hacked myself to death trying to solve this problem but still the updates go Astray.
Now each time a time is updated the process goes somthing like this
1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)
2. User Hits a sign in/out button
3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once)
3. The table is updated with the new time (using some dynamic SQL)
4. The table is repeatadly checked using a DO loop to make sure the the correct time went in.
5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens).
6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other)
7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.
I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.
Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..
None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.
Does anyone have any idea how these updates/inserts can go missing or create #ERRORs.
I've built plenty of Databases in my time and have never come across this.
__________________________________________________ ______________
This is the function I use to add a record to tblBugfixingLog and tblSQLRecord
Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue)
Dim TempSQL As String
TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _
"#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _
"#" & Now & "#," & _
"'" & FieldUpdating & "'," & _
"'" & NewFieldValue & "'," & _
"'" & GetNTUser & "'," & _
"'" & TimesheetNumber & "'," & _
"'" & fOSMachineName & "')"
' MsgBox TempSQL
DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False
'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table
DoCmd.RunSQL TempSQL, False
End Sub
Public Function CleanData(ByVal DataToClean As String)
Dim TempData As String
Dim i As Integer
TempData = ""
For i = 1 To Len(DataToClean)
Select Case Mid(DataToClean, i, 1)
Case "'"
TempData = TempData & "`"
Case """"
TempData = TempData & "`"
Case Else
TempData = TempData & Mid(DataToClean, i, 1)
End Select
Next i
CleanData = TempData
End Function
__________________________________________________ ____
I have no idea how this can create #ERROR lines in the table when it is just added to and nothing else.
Does anyone have any clue to what may be happening here.
(Oh yeah and no matter how hard I try, I can't replicate the problem.... works for me every time no matter how harse I am to it!)
Please save what little hair I have left and give me some hope
Cheers
Homer
Hi!
I hope this is the right section for posting this Q.
I use Access 2003 on WinXP pro as front-end & for back-end a MySql on a Linux server. I use MyODBC to connect to the back-end and all the tables are on the back-end. The workstation is connected to the server via VPN (so the server and the station are on different locations).
Quite often I get a problem that not all records are inserted into a table. E.g. I have like 5 - 15 records (up to 10 fields) in one table and I want to transfer/copy them to another:
strSql = "INSERT INTO tblDetailNakup " & _
"SELECT tblDetailNakupTemp.* " & _
"FROM tblDetailNakupTemp;"
docmd.runsql strSql
most of the time it works OK, but from time to time a couple of records are missing.
What could be the problem? Is there any way for somekind of a check, if all has been inserted otherwise the query is repeated?
TNX in advance,
Miha
Hello people. Iam having a problem and i need some help.
iam appending data in one table to data in another another.
I have two tables(table1 and table2), with the same fields but the records in one table2 are 5 more than those in table1 which are 3.
The 3 records in table1 are also in table2.
I would like a query which outputs the other 2 records that are in table2 and not in table1.
Thanks in advance.
i have a query that contains two tables one contains the member details the other their transactions. the query is to show all members and transactions however if there is no transaction then the member details do not show - -the query only shows members with transactions . the query does have grouping to give totals of the transactions
View 2 Replies View RelatedHi hope you can help me out,
I have a query which combines several linked tables, the query has about 10 columns, I need to show only the records which have one or more empty fields.
Some records may only have one missing field others may have several.
Any ideas?
Many thanks,
Ed
Hello again
Sorry, just posted this in reports, moved it now.
Could someone point me in the right direction please. I have seen a thread similar to this before but I can't find it now.
I have a query that is made up from 3 different queries.
These 3 queries show the stats totals in the last x weeks.
qry2Week (colleague stats from last 2 Weeks)
qry6Week (colleague stats from last 6 Weeks)
qry13Week (colleague stats from last 13 Weeks)
This query shows every day and also the above 3 queries are linked in.
qryAll (every day and 2, 6, 13week stats totals)
The problem is if there are no stats in the last 2 weeks no stats will show up at all.
If I take out the (is not null) from the individual queries I get duplicate lines in my main query (qryAll)
How do I show all the information without having duplicate lines.
Cheers
Kev.
Hi.
i.e..............
Table A has records 1,2,3,4,5,6,7,8,9,10
Table B has records 1,2,3,4,5,6
How do I create a query that returns values 7,8,9,10 for Table B when I compare Table A and Table B?
What sort of query do I need? I tried the "Unmatched query" but this did not get the result I wanted.
Thanks for reading.....
Frank.
I have a table for stock with two fields, KEY and DESCRIPTION; a table for manufacturers with two fields, KEY and NAME; and a third table which links to them both with a many-to-one join with three fields, KEY, STOCK-KEY and MFR-KEY. What I am trying to do is write a query that for a given manufacturer (entered via a parameter) shows a single line for all stock records that are NOT linked to it via the third table. I am sure it should be simple but all my attempts fail to exclude stock linked to the manufacturer if it is also linked to another manufacturer. Any ideas?
View 8 Replies View RelatedI am working with a normalized database that has MANY tables. Most of these consist of lists of options to select from for the primary table. The primary table is linked to the secondary tables (and those to tertiary tables) by Primary Key ID fields, and the other tables contain additional information.
For Example, the Name list table is related to the Actions table by the SSN field, and the Actions table is related to the PayStatus table by an ID number collected by a lookup field.
When I pull all three tables into a query to display all the information related to a particular individual an his action, there are more records if I remove the PayStatus table from the query. It seems to only pull reports for which a PayStatus has been selected.
How can I get the query to display ALL the records, whether or not the individual has a pay status? Whether or not the individual has one is irrelevant, but I want his name to be displayed, whether or not he has one.
Thanks for any suggestions.
I've got a database used daily by 4 users. It's split into a frontend (10MB) with all the forms/queries/reports/vba and a backend that's just tables (170MB), and the users access the database from a network drive. All additions through the forms are logged to a text file, and at the end of the day, a report is run that prints the day's work to a PDF. The database / workflow has been stable for the last few years, with only minor edits to the code, and no programmatic changes in the last 3 months.
Today, after running a compact-and-repair, I realized that the database contained no entries added within the last 2 weeks. I checked my log files, and sure enough I see that all of my records were at some point added to the database. This is supported by the fact that I have PDFs for every day in the last 2 weeks that show exactly what was done (roughly 30 new records/day).
My first guess was that compact and repair had corrupted the database, and knocked out a chunk of records. Fortunately, I've got daily backups, so I started restoring to yesterday's database. At this point I found that the records were missing from there, and from every backup from the last 2 weeks. Now, it's possible that my backup solution (logMeIn backup) is hosed, but the the log files are getting properly restored by the backup, which leads me to believe the backup is working. So, somehow these records were never saved in the database, yet they magically appeared in my end-of-day reports?
I thought maybe I was getting stuck in some state where the database went read-only and the edits were getting stored in memory but never written to disk, but that doesn't make sense as we occasionally restart the database during the day for other reasons, and the end-of-day reports are always complete, which knocks that out. Having restored to a prior version of the DB, I attempted to make changes / add new records and they appear to be sticking, but I find my faith in Access rather shaken, all the more so because I haven't a clue what went wrong before.
I have a combo box and button on a form that should open another form and display the results in a datasheet view filtered by the combo box selection. The second form is based on a query that has the following criteria in the JobType field:
Code:
Form!frmReportView!cboType
When I run the query it correctly prompts me to enter a value for the criteria and displays the proper results. Likewise, the same thing occurs when I run the second form independently. The problem is when I try to run it with the combo box and button. The second form opens in a datasheet view with the headings, but no detail records are being displayed.
Hello All,
I have three tables: Employees, Gender, Diversity. Both the Gender and Diversity tables are one to many relationships with the Employees Table. I am trying to run a query that will output a count of all diversities and genders. For example:
Diversity-----Gender-----Employee Count
White----------Male---------------1
White----------Female------------5
Asian----------Male---------------3
Asian----------Female----------<Blank>
Hispanic-------Male-------------<Blank>
Hispanic-------Female----------<Blank>
I am trying to get all counts, even if the combination of diversity/gender is not in the employees table. I am going to use that information in a Crosstab query.
What I thought would work was do a Left Join For Diversity and Employee such as:
SELECT Diversity.[Diversity Description], Count(Employee.ID) AS CountOfID FROM Diversity LEFT JOIN Employee ON Diversity.ID = Employee.Diversity GROUP BY Diversity.[Diversity Description];
Then, do a Left Join for Gender and Employee such as:
SELECT Gender.[Gender Description], Count(Employee.ID) AS CountOfID
FROM Gender LEFT JOIN Employee ON Gender.[Gender ID] = Employee.Gender GROUP BY Gender.[Gender Description];
And then do a Union. But that doesn't work.
Any thoughts or comments would be much appreciated!
Thanks,
Jon
I have an application where I have to read a big VCF file in VBA, extract the information and place it into records of a table. Of course I can read as a text file, but the structure is quite cumbersome to implement with many fields missing in different records and it is not a one time job.
View 1 Replies View RelatedI have a main form with 3 subforms. Each subform is identical except for the value of the filter property. The filter is for the same field, but with a different value for each subform. So, for example, the first subform has a filter of:
Code:
[WBS Element]="DEF" And [Period]=Forms!frm_ProjectFinancials!Period
while the second subform has a filter of:
Code:
[WBS Element]="PPE" And [Period]=Forms!frm_ProjectFinancials!Period
and the third subform has a filter of:
Code:
[WBS Element]="EXP" And [Period]=Forms!frm_ProjectFinancials!Period
The recordset for each subform results in a single record with numeric values in each field or no records at all. When the resulting recordset is empty (no records), the bound text fields on the subform display as blank. I want these fields to display 0 instead of blank so I can use them in other calculated fields. Functions such as Nz or IsNumeric do not work since there are no records and the values are neither null nor numeric.
How I can display zeroes in the bound fields when no records exist that meet the filter criteria? Or is there a way that I can dummy a resulting recordset to have all zero values when there would otherwise be no records?
I have a query that will draw down student details who have completed a course in a given month (May for example), i would like to use this data to identify those learners who are not enrolled on a course in the next Month (June for example). There is no field that denotes whether a student has left only that a course assosciated with their ID has a completion date within that Month. There are approx 250 records.
In my head it should work something like this
1) Identify all learners who finsihed a course in May (Identify learner ID, must have a course end date in that month)
2)Cross reference these against all those who started a course in June and identify the students that have completed in May but did not start a course in June.
Is it possible to store all those who completed (May) in a table/query and cross reference those who started in June and identify of the May completions who did not start in June?
When I run this Query without the WHERE statement/Clause- it returns the accurate 985 records. However, when I include the criteria with the WHERE clause (it takes a range of +/- 10% of the Square Footage size into consideration), I lose 15 records, and only get 970 results. When I increase the range to +/- 15% in that field, it gives me 7 more records back.
Obviously it is dropping out records that do not meet the criteria for that field- how do I prevent that? I guess I would like it to display a ZERO or something, but still display the entire 985 records. What do I need to include to do that? Here's the SQL-
SELECT tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, Avg(tbSold.SoldPrice) AS AvgOfSoldPrice, Count(tbSold.SoldPrice) AS CountOfSoldPrice
FROM tbListing AS tbAct, tbListing AS tbSold
WHERE (((tbSold.SqFt) Between ([tbAct].[SqFt]+([tbAct].[SqFt]/100*10)) And ([tbAct].[SqFt]-([tbAct].[SqFt]/100*10))))
GROUP BY tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, tbSold.Status, tbSold.Area, tbSold.Level
HAVING (((tbAct.Status)="ACT") AND ((tbAct.City)="TEMPE") AND ((tbSold.Status)="CLOSD") AND ((tbSold.Area)=[tbAct].[Area]) AND ((tbSold.Level)=[tbAct].[Level]));
I hope someone can help me with this. I figure I'm missing one line of code but can't figure it out. I have 3 tables - tblGeneral (which is my main table), tblMDLink, and tblMD. The primary key in tbl General is FileNo. The primary key in tblMDLink is an autonumber and the primary key in tblMD is also an autonumber called MDID. tblMDLink is just a linking table containing the fields Fileno and MDID and uses an autonumber as its primary key. It contains some other fields that have nothing to do with this. I have a query called qryMDLink which contains all the fields of tblMDLink together with a calculated field calle full name which just combines the last name and first name fields of tblMD. Now I come to my form (which is actually a subfrom) called subfrmIMEDoctor. The purpose of the fomr is to allow users to add the names of various doctors to various files. The record source of the form is qryMDLink. The form also contains a FileNo field which is added programatically. The field has a drop down combo box with its record souce set to MDID. The drop down box has two columns with the first (not visible to the user) set to MDID and the second set to Full Name. Here's the problem - it won't let me enter data. Whenver I click on the box I get the message - "You cannot add or change a record because a related record is required in table tblIMEDoctor". By the way, I did set up one to many relationships beteen tblGeneral & tblIMELink and between tblIMEDoctor and tblIMELink. Any help would be greatly appresciated. Thanks,
Tom Gorton
I have a relatively large database 65M, 42K records.
For some reason I seemed to have lost most (not all) of the queries I have written. However, if I go to file>database properties>contents, I can see all my queries there.
I certainly did not delete them. Any I idea of what’s going on ? I'm, using Acess 2000 on win2K. Might this be a resources / memory related issue?
Thanks
Joe
Hi,
I have build a database which work perfectly on most computers. But one or two for the computer are error with the Left, Right and Date SQL function. Is it simplely miss for the install or it a problem with my code?
Thanks
I am running Access 2000. The control wizard is not available. It does not come up when I add a command button for example. How do I get it turned on?
View 6 Replies View RelatedHi,
when designing reports or forms, when I pressing the
toolbox icon, it would depress, but no toolbox was visible. I tried
resetting the toolbox (through view/toolbars) and I set all of the
settings back to default. I even uninstalled and reinstalled MS
Access several times but no luck.
Any help please.
Gunawan.
Recently I lost the the functionality of the toolbox then shortly after I lost the toolbox icon completely.
The only way to use the features was to create an new toolbar.
System Restore finally fixed the problem.
This has happened twice on me with the 200 and 2003 versions.
Is it me? and is there an easier way to sort this. Reinstalling Access never cured the problem either.
Ta
Houston, I need some answers.
I don’t have one of those MSysObjects. Now how do I get one and how do I populate it?
I have a database that has just been split. In the back end, I don't see the tables. I have gone to Tools>Options>View and found that Show Hidden Objects is checked. When I go to the front end, the links to the tables show up and I can open them and see the data. When I check the Linked Table Manager, I see that the path for the links is leading to the back end database.
I also noticed that on the bottom of the screen, the buttons for the front end show the name of the front end database and have blue page-like icons, but the button for the back end has only the pink key icon. Also, when I try to link another mdb to these tables, the window for selecting the table for linking is empty. I also notice that suddenly, any time I close an Access database, it goes through a compact and repair cycle. This just started happening today. I'm fairly sure that yesterday I could see the tables in the back end.
Oh, yes. Just remembered one other thing. Not everyone here has that location on the network mapped to the same drive. In order to accomodate everyone, the linked table manager was set up with the complete path to the folder:
\companyname.comfolderlevel1folderlevel2folder level3...ackend.mdb instead of
J:.....
Using Windows XP and Office 2003
There is no verticle scroll bar as was mentioned in another post I found when doing a search on "Hidden Objects".
Any ideas where I should try next?
Hi,
i've developed a program in access 2003, but now i'm on access 2000 and seems that one / more library is/are missing..
i've this part of code
Private Sub List0_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim strOrd As String, strSql As String
If Button = 1 Then
If Y <= 225 Then
If Right$(List0.RowSource, 5) = " Asc;" Then
strOrd = " Desc;"
Else
strOrd = " Asc;"
End If
strSql = "SELECT cliente.CLI_ID, cliente.CLI_TITOLO, cliente.Cognome, cliente.Nome, cliente.Indirizzo, cliente.Cap, cliente.[Citta'], cliente.Regione, cliente.Nazione, cliente.CLI_SOCIO FROM cliente WHERE (((cliente.CLI_SOCIO)=Yes)) ORDER BY "
Select Case X
and this is the screen with the libraries currently installed ..
http://img246.imageshack.us/img246/7383/libreriaum5.th.jpg (http://img246.imageshack.us/my.php?image=libreriaum5.jpg)
i was wondering which one is missing....i'm quite far from my laptop right now..
thanks in advance
bye