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 have two tables
table1 his a master list of companies that I have already delt with
table2 is a list of companies that I have delt with as well as new companies and I allow for multiple instances of company data to be in table2
table2 my have several records refering to "ABCD Inc" but table1 will only have it once
If new companies show up in table2 how can I get a mass copy of all the companies that are in table2 but not in table1 into table1?
thanks,
honor
I have all my data sitting on a server in SQL. Within Access, I have linked tables pointing at SQL tables. I display the data on an Access form. Somewhere in their it is dropped a few records. This system is used for in and out processing and occasionally misses a few individuals. Could this have anything to do with the method in opening the table, ie dbopenTable vs dbOpenSnapshot.
View 1 Replies View RelatedHow to fix some records in my access table. It is a huge table more than 12k records!
In one of the field there are some data missing. The logic to reconstruct them is easy but I am not sure how to apply it in Access.
I have three columns one is the student ID, Year, term1 and term2
ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003
3311 2004 005 005
In the above example student 1234 has a missing record in year 2004 which supposed to be Term2 in the previous year (i.e. 003) and Term2 supposed to be Term1 value in the following year (i.e. 004). Similarly for student 3311
ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004 003 004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003 004 005
3311 2004 005 005
I have created query and all my values come into the table from the query fine.
When i then go and generate the report all of the boxes are there for the data but there is no data in them. For owner and Job Id all info is there but job name, Department and a few others there is no data in the boxes.
Hi,
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
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?
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 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.
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
I want to use a column in a query to show where data is missing in other fields.
In excel I have used this statement:
=IF(COUNTA(I5:J5)=2,"","error")
Basically, I have two fields PRICE and WEIGHT. I want a column in the query to show 'Error' (or any kind of flag) when either (or both) of these fields are blank.
Hope this makes sence.
Any advice?
Thanks
I would like to know how to display a msg box for missing or duplicate information when entering data into a form. My database keeps track of call accounting codes and the code is the primary key index field. I use macro's for all of my automated tasks such as finding codes, filtering for available codes and such. What I want to do is to display a simple message when adding a new one to the database if they forgot to enter the code and if they did enter a code if they entered a duplicate. The default error meesage tells them there is a problem but they won't know how to fix it. I know I should use the Before Update and After Update but I am not good at writing the vb code. I found some code in one of the the threads and copied it and changed the field names but it failed. I need a simple message like "You did not enter the Fac code" and "The Fac code you entered is already in the database please check your information."
The field name of my primary key is "Fac"
Thanks in advance for anyone that could help me with this.
I have an issue with saving PDF report
When i preview, i see all data just fine but when i save a PDF of my report, i am missing some data in the report. That data is rather important because its the quantities and size of my items in my Purchase Order Report.
I do see the items description.
I currently have query that looks at 2 tables that hold financial information. I am querying the data to do some adding and subtracting based off a financial class of a facility. The issue I am having is that one table may have a financial code that the other does not have but I still need to show that financial class for the facility.
My looks as follows
FROM [CashValue Link] INNER JOIN TCashValue ON ([CashValue Link].FINANCIAL = [TCashValue].FINANCIAL) and ([CashValue Link].Date1 = TCashValue.Date1) AND ([CashValue Link].FACILITY = TCashValue.FACILITY) AND ([CashValue Link].CLT = TCashValue.CLT)
I am sure I am over looking something.
I have a master table that holds all of my data. The table details what qualifications someone is holding.I would like a query that would enable me to produce a list of people who DO NOT hold a qualification.
View 8 Replies View RelatedBelow is a text file that we need to import from a POS system but the layout is not conducive to a typical database import. 2500 series of numbers are in the second field of the table and I would like to add that account number to the second field for each row and I would like to add the date 4 the field of each line but not sure how I would accomplish this.
" "|"25000.....0793"
" "|" "|"Name of city, R1"|"01/25/2014 17:31:43"|80079| 21.23|"BROCCOLI"| 0.00|"Client"|1
" "|" "|""|""|""|""|"SALMON OSCAR"| 19.59|""|""
" "|" "|""|""|""|""|"WATER"| 0.00|""|""
" "|" "|""|""|""|""|"WILD RICE"| 0.00|""|""
" "|" "|""|"01/25/2014 18:26:56"|30060| 26.02|"***AS APP***"| 0.00|"Server"|1
[code]....
Hi there
I have some a little programing in microsoft access vb and so I am here asking for advice.
I need to copy a data value from one field down the list of null entries till it reaches the next value and then use that an so on in a loop.
E.G
[Area Code]
199a
null
null
null
null
132f
null
null
null
Regards
Luke
Access 2010 report based on query based on 3 views using ODBC with the following sections:
Report Header
Page Header
Detail (1 - 3 lines)
Page Footer
The detail section has these lines...first line exits for all students, other 2 lines optional. Each line includes data from each of the 3 views:
Demograhic and bus/route information
Special needs information
Additional text information
Each student may have multiple lines in the detail section, one for pickup route, one for drop-off route and possible other routes.
Using VBA and the On Format event of the Detail Section, I have formatted the report to not display repeating information (not apply to use "Hide duplicates" feature) and not display either/both of the optional lines in the detail section.
All works well except that on the first line of each subsequent page, most of the data is missing. I have stepped through using the debugger and see that the data is returned by the query, but not displayed on the report.
I am getting two different results when I preview or print a report. When I preview a report the report title and page title are populated with the strings I built and assigned to global variables. In the report load event the GVs are assigned to the text boxes. Everything works great. If I hit the print icon while previewing the report, those text boxes are populated with the right strings. The problem is I have the option to send the report directly to the printer without previewing it, using the code below.
Code:
DoCmd.OpenReport Report, acViewNormal, , , acHidden
When this is executed, the report title and page title text boxes are blank. I have put a debug.print (the 3 GVs) in the load event and it spits out the three strings, but the actual printout has no titles. The bound data always prints, but these unbound text boxes fail. I have even copied the string assignment to the open event and that did not work.
I have 2 tables: one for repairs and the other for the billing for those repairs. There is a foreign key(record_num) in the billing table to match the primary key(prikey) in the repairs table. This works fine as long as the unit repair has been completed.
Now an employee wants to see records even if they are not completed and wants the rate to be $0.00 if the unit has not been completed. But by this method there is no record in the billing table.
My problem is if I have the 2 tables joined then I only see records that match both tables. Here is my SQL for the query:
SELECT DISTINCTROW tbl_module_repairs.end_user, tbl_module_repairs.pickup_date, tbl_module_repairs.complete_date, IIf([pickup_entity]="Storm","APS Storm","APS Field Tech") AS [Repair Pickup], tbl_module_repairs.mfg_part_num, tbl_module_repairs.manufacturer, tbl_module_repairs.module_type, tbl_module_repairs.incoming_module_sn,
[Code] ....
I work in an office where we use the same database in a folder on a shared server. When Mary Jane is signed on to her computer she can't see all of the data for a record but I am able to see all of the data for the same record on my computer. So I had Mary Jane log out of windows on her computer and I logged onto window on her computer. Once I was logged onto windows on Mary Jane's computer, I then opened the database in the same folder on the shared server and I was able to see all of the data for that record.
View 1 Replies View RelatedWhen data is input to my form and when i click on preview report, the preview report displays all results (my report source is from my query). However when I click on print preview a portion of data is missing. Basically is the check boxes I have in the report. They all are grayed out. These check boxes are unbounded and not related to the query. Basically I have an event procedure created upon clicking print preview as follows:
Private Sub PreviewMRO_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rptMRODataEntry", acViewReport
If MROReason = "Repair" Then
[Code] ....
The selections are all drop down boxes in the form and upon selection it will populate the text boxes in my report. It seems to work perfectly upon clicking the preview report button but when I click on print preview the check boxes are grayed out.
I'm trying to make a sub form that displays the hours of an employee selected in a listbox. I've got most of it working but having a bit of an issue.
The info for thre query is in 3 tables:
tblStaff (name etc)
tblShifts (start and end times for days that this employee works)
tblDays (a list of days names so I can use numbers elsewhere)
My query looks like this:
Code:
SELECT tblDays.dayName, IIf(Nz([startTime],"")="","NWD",[startTime]) AS start, IIf(Nz([endTime],"")="","NWD",[endTime]) AS [end], tblStaff.staffName
FROM tblDays LEFT JOIN (tblStaff RIGHT JOIN tblShifts ON tblStaff.staffPK = tblShifts.staffFK) ON tblDays.dayPK = tblShifts.workingDay
WHERE (((tblStaff.staffName)=[Forms]![frmMain]![lst_myTeam] Or (tblStaff.staffName) Is Null));
This worked fine with a single user and some test data - it correctly displayed all days of the week, with start/end times on Mon and Tues where I had entered shift information, and "NWD" against all other days.
However, when I add a couple more employees to the mix it shows the correct info for the first employee, but anybody else it will only display days where person 1 doesn't have any hours. I haven't entered any hours for the new employees, but the query should still display Mon-Sun with NWD in every column. It shows Wed-Sun but Mon and Tues are missing.
I've tried different join types but they all come back with "ambiguous joins" error when I try to run.
I have an access database with several linked tables (linked to MySQL database) and several local tables. The theory is that if there's ever a connection issue, the device connected to the computer will continue logging data to the local tables. Once a connection is re-established, the linked tables should be updated with all the missing records which appear on the local tables.
I found several possibilities which I outlined below, but I've been asked to investigate whether there's a built in function in access that does this for you and use the other options as a last resort. Does access have any program feature that updates one table with missing data from another table, or will I have to write VBA code to do that? Options I've discovered:
1) Write unmatched query and insert missing data into table.
2) Create a linked table on MySQL that will link to the local tables on access, then compare the records there.The boss isn't happy with those options because he wants to keep the amount of code we add to a minimum. Ultimately, we hope that a program feature that does this is built in to access. If not, I have no problem adding code to do this instead.
I've got an Excel sheet with +700k rows and 20 columns that I wanted to import to Access. All fields are text except the field that I want to use as a primary key, but I planned to import that as a text as well.
When I used the import wizard, I set all fields to import as text except for three that I set to memo. The wizard didn't say there was any error after importing the data, but when I checked the table, I noticed there were *a lot* of records where many fields where blank. Some fields where completely unaffected by this problem throughout the entire table, but in the rest of them, there is data missing in many records, and when there is data missing, it is not always the same fields that are missing. I have been unable to find any pattern that explains why sometimes the records were imported correctly, and why sometimes they were not.
I was wondering if there was a special character you entered into the column area if you have missing data.
For example,
I am trying to run some statistical analyses using a pivot chart. I want to make sure that every box in a specific column is filled. For some of rows I am missing numbers for some of there columns. If there is missing data do I put a wild card?
EXAMPLE:
First Last Middle Average
John Smith Adam 100
Smith John Peter (Blank) <--- no data
(Now if I were to find the average of both it wont run)