Hi 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
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 two tables T1 , T2 each have one field Invoice Number in T1 and
Invoice Number in T2 , lets say in each table in each field : T1 : 1,2 and 3
T2 : 1 and 2
So if I want to find records in T1 which is not in T2 what can I do?
I think this would be done though a query but i am not sure, any help or examples would be useful on this on.
Question:
Is there a way of getting access to find missing numbers. for example if some one enters 0 - 5 then access will pull the following numbers 1,2,3 & 4.
I am not sure if this is possible so any help would be good.
Alastair
P.S. HAPPY NEW YEAR TO ALL.
I have two tables. The first one lists all of the courses that a student has taken. The second table list all available courses. I need a query that will tell me which courses the student has not taken. I tried using the find unmatched query to no success. Any help will be greatly appreciated.
View 5 Replies View RelatedI need to write a query to find and match all numbers recorded against a list of numbers (1 - 200) and to identify those missing or not matching.
View 1 Replies View RelatedHi 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
Hello
Could someone please help, At the company i work we use an access 97 application, which was made by an old tech who does not work here anymore
It is just an mdb file, 97 format, with forms, tables, sql etc
Normally i can just install access 97 and it works on everyones computer, apart from one..
It loads up some forms, however when editing, and a subform if clicked on and change, when saved an error comes up
"OLE SERVER ISNIT INSTALL, REINSTALL IT" THEN IT CRASHES
seems to be just on this one object
the codes fine because it works on everyones else, it there a way i can find out what dll files are need for this mdb to run ? anyone got any ideas
i dont have office devoloper, so i cant make a installer for it
i have tryed install access 97, office 97, office 2003, and works but this ole or object never seems to be installed ?
any help would be great, i cant work this one out
I have a table where csv files get imported to on a daily basis. The key fields I am working with are a supply number and date. The problem I have is that sometimes the csv file will contain information that is correcting/updating information held in the table. This is creating duplicated records.I need to either create a query to find records that have the same supply number and date or find a way of overwriting the records in the table with the new data.
View 2 Replies View RelatedThis may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.
My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.
My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.
I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.
The plan being to delete out those that are identified by the query.
I am trying to create a query to find missing sequential numbers in a text field. I am using this specific field as a case number which is designated as two letters, the # sign, two digits indicating the year, a dash, and then a four digit number; For Example: AB#13-1234.
The reason for this query is to tell the user of this database that a specific case number has yet to be entered and needs to be. The case numbers are unique and will never be referenced more than once.
My table name is "MainDataTbl" and the field i'm trying to find the missing case numbers is titled "CaseNumber".
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!
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 RelatedHello 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.
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 RelatedHi,
I have a form that collects data, it works perfectly and writes all inputted data to the table. I have made the same form but using the data web page wizard.
It works fine but when i look at the table, some of the data is missing. there are 12 fields to complete, (all drop down boxes) I have one table which contains the employees ID number and name.
When the id number and name and picked from the drop down box on the web page, they are listed and fine. The record gets added and no problem.
However, go an look at the data in the table and for some reason the employee ID number is present but the name field is blank?? It has not copied the data across.
Even more confusing (currently a test system) if i delete all the data in the table, it works for the first entry, i.e the web page populates the name field but only for the first entry. After that the name field remails blank.
Any suggestions? I have already deleted and re added all the table fields that relate to name and id number and still the same error. I have used the wizard to make several pages and the same problem with all of them. I am baffled why it just refuses the one field and all the others are fine.
thanks
Hi! I'm new here and have a little bit of experience w/ access...
I have a problem... I've created a form in my db but when I go to view it (not in design view) the information in the detail area is not there? The problem is not unique to this db either, I have this problem in another... Any ideas? All help is appreciated.
~Ashley
I have system that developed by using Ms Access which has been used for 2 years.
But last week, data in one of my table missing in the half way when user do the data entry.
There is header table and the details table in the form. All record of the header table were gone
but the details record was not problem.
Why all data in table were missing?
Please give some guidance.
Thanks
tee
I am a beginner using Access 2007. The custom written program that I am using has a minor glitch. The report has a subform inside the main report. In this subform is a form header, detail, form footer. The information that is entered into the main report that transfers to the form header, detail, and form footer works and that data is saved with no problems. When I go to create a pdf file from this report all the data transfers to the printed pdf except the information in the form footer. I have looked at the property pages and compared all the properties and they are all identical.
Below is a screen shot of the report in the design view. The areas circled in red are the data that is missing from the pdf when printed. The area in green prints correctly as well as the main body of the report. We insert comments in the comment section and also type initials in the signature section but this do not show up when printed in the pdf. The database saves the information that is input but does not print it. Not everything in the company has access to the database so this is the reasoning for generating a pdf so that it is placed on the company drive for everyone to see.