I have 2 tbl's one is class and the other is professor... in my logic i create a new class for each student however, I am trying to make label for the professor and since a professor can teach more than one class i need only one label. How do i do this
View RepliesI have code written which imports excel data to a access table but after the first import it fails due to duplicates, how can i tell it to ignore duplicates in the table and only copy new records?
code is below.
Code:
Function SyncEmployes()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
[Code].....
Hi there
I've been looking for this one for a while. In my query I'm using a date field to extract dates using the between And function. I'm refrencing to txt boxes within a dialog form within the query.
For example:
Between [Forms]![frmViewByCatagory]![TxtStartDate] And [Forms]![frmViewByCatagory]![txtEndDate]
The field actually only lists years rather whole dates .
I want to find a way to add an expression so that I can leave the txt boxes blank and access will ingnore the txt boxes and give me all the records.
Does anyone have an idea what I can do here. I thought the IIF function along with Null might work but I don't know the syntax.
Thanks
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
Hi guys, is there any code u can write that ignores this hidden command that closes the form when you press the X button in the top right corner of the form.
Many thanks, Ben
Hey
I have a form where the user can choose from a drop down menu, values like Name= James.90
Date=1/10/07.
Then it would return the record with James.90 And That date in it
But my problem is you have to choose a value for both boxes. Is there a way to ignore it if the field is left blank.
My current code is
SELECT timesheet.WeekEnding, timesheet.FName, timesheet.Company, timesheet.Project, timesheet.CTR, timesheet.Comments, timesheet.Total, timesheet.Project_Title
FROM timesheet
WHERE (((timesheet.WeekEnding)=[Forms]![FormBeta]![Week]) AND ((timesheet.Company)=[Forms]![FormBeta]![Company]) AND ((timesheet.Project)=[Forms]![FormBeta]![Project]));
In my database I have module that checks if tick box is True and then closes all the forms and kicks the user out of the database. However in most of my forms, there is a custom Save command in the On Click of cmdSave button and the Form_BeforeUpdate. The save requires certain things to be true so naturally if a user has made a change to a record, the form won't allow you to close it if you haven't saved your changes or undone them.
Is there anyway I can get my module to bypass certain events in the form?
I have some expressions built to calculate totals in a query. Here is the first
Engineer Total: [Engineer Hrs]*[Engineer rate].
This works when a user adds hrs to a table and the rate is a default number.Then a total from 4 totals similar to the Engineer total are added together here:
Total Rate: [Designer Total]+[Quality Total]+[Coordinator Total]+[Engineer Total].
Works perfect if all 4 Hrs fields have data. The problem is not every entry will have hrs in each field..when this happens the Total rate does not calculate. So long story short, how do I make the Total rate bypass the blanks or do I need to make the Engineer total force a zero on blanks?
In a database am building, I want to run a query with the criteria dependant on which field the user populates in a form.
The form has a number of fields that the user can select from including our reference number, the client's reference number and the site address.
I would like the user to be able to select the site address using a wildcard so that they can enter a part of the address such as "This Street" instead of "45 This Street" and the user be presented with all of the records matching "This Street".
I tried using the criteria:
Code:
Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"
Which works perfectly as long as this field is populated. If this field is not populated, entering details in any other field bring up every record in the database.
Code:
IIf(IsNull([Forms]![SearchJobs]![SearchAddressLine1]),Null,[Forms]![SearchJobs]![SearchAddressLine1] & "*")
The full sql of my query is:
Code:
SELECT Jobs.JobsSalesEnquiryRecordNumber, Jobs.JobsJobStatus, Jobs.JobsEnquiryDate, Jobs.JobsTakenBy, ClientDetails.ClientDetailsURN, ClientDetails.ClientDetailsName, ClientDetails.ClientDetailsAddressLine1, ClientDetails.ClientDetailsAddressLine2, ClientDetails.ClientDetailsAddressLine3, ClientDetails.ClientClientPostCode, Jobs.JobsClientJobNumber, Jobs.JobsAlternativeClientJobNumber, Jobs.JobsClientContact,
[Code] ....
I have 3 queries populating 3 subreports on a report, i have a checkbox on my main form i launch the report from - i reference it in a filter on the form to ignore certain error types - id like it do to the same in the queries that populate the reports
So the reports atm give - an over all count of error types, a count per person and then a breakdown on type per person
Thats all fine but id like the option to ignore one type of error (as we sometimes look at it and sometimes dont).
The problem I am having is I am using a =Sum([SumOfCountOfUseData]) statement to get a number of instances data is used from a table this works with one exception it counts deleted instances and sums those as well?
View 7 Replies View RelatedI'm using access 2007 and have some date fields in my forms, i'd like to be able to blank out weekends and public holidays from the calender/date selector in these fields so theres no way those dates can be selected. Is this possible? Thanks.
View 1 Replies View RelatedHi,
I have been tasked with completing an audit of approximately 10,000 items, to which I have generated a list of 40 questions (fields) for each record. I began collating the information in Excel, but found myself getting lost and even experienced major data loss on at least 2 occasions. I have now decided to ditch Excel and use Access 2003 instead.
I have imported my master spreadsheet, however as I have various contacts sending in their respective information in Excel spreadsheets with same types of fields, and also need to import data that has already been sent in. I’m thinking that it would be better for me to create update and append queries, especially as there is going to be a stage 2, where I will be requesting additional (field) information.
There is a big chance that some of the contacts will send in info for the same item (record), which means that there is a risk of duplication, which I have removed as I have a unique identifier which will be the primary key! Is that right or should it be indexed?
The main problem that I have is that I want Access to ignore the target cell if it has a value in it! Meaning that I would not like Access 2003 to overwrite the cell with valid data in it, with a blank cell! As I need the database to grow!
Can anyone suggest a way that I can do this please.
TIA
Bazdaa
So I have a report with two pages. One page is about product exchange information and the other one payment. This report can either be printed or saved as a pdf (one pdf for both pages) and e-mailed.
This works fine, however if the item is under warranty then the exchange is for free and I do not want to print/e-mail the 2nd page (payment).My trigger field for this is "Warranty" which can be "Yes" or "No".How can I prevent that 2nd page from printing/e-mailing? Can it be simply skipped when the report is being created based on my field?
In my table I have a numerical field for case numbers. Our case numbers are in the format of YYMM000000 (YY = 2 digit year, MM = 2 digit month, remaining 0's are consecutive case numbers). In order to properly list the case numbers in descending order, I have
caseyr: Left([CaseNo],2) (which pulls the first 2 digits being the year)
officeno: Right([CaseNo],3) (which pulls the last 3 for our part of the case number)
This was working fantastic until I had to enter cases from 2009, ex: 0911000587
I have set the format for the CaseNo field to 000000000 everywhere it is. It displays the 09 cases perfectly, retaining the zero, however, my left function ignores it. In the case number of 0911000587, it pulls 91 as the first 2 digits. So, in the form that I list my cases in order, the 2009 cases are showing up first (because they're pulling as 91 instead of 09).
Is there anything I can do to force it to NOT ignore the leading zero?
I have created a ribbon for the users on all forms, and named that ribbon on each form. While I can bypass the Main ribbon (hiding backstage and most other tabs) by holding the shift key on open, the minute I open a form I am stuck with the very limited form ribbon I've named. Is there a programmatic way I can ignore this call, either when holding the shift key or some other avenue?
View 14 Replies View RelatedSuppose I have a table with a variety of fields; one autonumber, some text fields and some date fields.
I need code to calculate the number of fields that are NOT date fields and then code to count how many of those fields (the ones that are not date fields) have data in them.
Following is the code to count how many fields in a table had data and this works well, but now I need to find a way to ignore the date fields. How can this code be modified to do the same but ignore the date fields?
Code:
Public Function PercentCompleted(Table As String, IDProject As Long, TotalFields As Single) As Double
Dim sampO As DAO.Recordset, iCtr As Long, strSQL As String, Percent As Double
'Takes the fields based on the projectID
strSQL = "SELECT *" & _
"FROM " & Table & " WHERE IDProject = " & IDProject
Set sampO = CurrentDb.OpenRecordset(strSQL)
[code]....
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
Thanks
Will
I have an Access table with an indexed key that is currently set to duplicates OK. There are not supposed to be any duplicates in that field. But it is possible than a small number have crept through.
Two questions:
1. What would happen to those duplicate records if I changed the setting from duplicates OK to no duplicates?
2. Is there any way to ferret out those duplicate records first and change them manually? The table currently has 48000 records so it would be a pain to go through them page by page. (I know how to export them to Excel, where the dups can easily be found; I was just wondering if something could do it within Access.)
Hi,
Wonder if someone can help please. I'm quite new to Access so please bear with me.
I have a data field in my database consisting of both a date and time.
I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding.
The problem is that because the field contains a time it doesn't return any records when I run the query.
Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above.
Many thanks
Chris
Hi. I have two tables, of which i have run a duplicate query, removed the duplicates by doing an "is null" criteria, and keeping that new table.
I know though there are still more duplicates but the names may not be exactly the same, say barclays and barclays plc. so i run another query on both tables to show me the first five letters of table 1 and table 2. I can run another "is null" criteria query to find the non duplicates there. these are the unique values.
But even if there are duplicates up to the first five letters the next letter may change, for instance barcleys tractors and barclays bank. So these arent duplicates.
So can anyone come up with a way of taking the duplicate ones according to the first five letters, and maybe duplicate ones up to 10 letters, and working out which ones are the unique ones. my brain is frying.
thanks
alex
Hi
I have imported a 1 column table from an external source for the sole purpose of deleting duplicates. There are over 400,000 rows. What do i need to do to identify the duplicates?
Thanks
I work at a hospital and deal with several thousand account numbers a day. I am working on an Access database that will handle some of my workload. Here is what I am facing. Lets say on some of the tables that I import there are duplicate account numbers with different totals in the end...
acct info info info amount
123456 xxxxx xxxxx xxxx 5.50
123456 xxxxx xxxxx xxxx 17.50
123456 xxxxx xxxxx xxxx 900.00
753951 xxxxx xxxxx xxxx xxxxxxxx
and so on...
is there a way that i can only show one account number but the total of the amount for the account? please help...this is desperately needed.
JG
Hey All, i'm looking for some help from one of the heavy hitters in the Access Forum. (Keep in mind i'm self-taught in access..never picked up a book on the stuff) So far i have done ok. I'v made many databases and in my opinion i have a firm grasp on the fundamentals.
I have come accross a database that I did not create which i am suppose to work on. I can do the work just find but i'm a little confused on the structure. when i looked at the relationships i noticed that many of the field names in several tables were bold (showing they were set as a primary key) So i looked at the design view of a few of these tables and noted that the primary key was set on 3 different fields. i also noticed that each field allowed duplication. Furthermore i noticed that on at least one of the tables, in the relationship window there was a relationship using the ID field which was a 1 to many. however there was a relationship on the other side of that table still using the ID field that was a many to one. How can a field have a one to many relationship and a many to one?? it can either be duplicated or it can't...right???
Anyways i also tried to duplicate this in a test database and found that when you make a table and then right click at the side of the field you wish to make as Primary key, that works fine, but doing it on another field simply switches the primary key to that field and takes it off the first one.
(as a side bar..is the only way to search this forum to go manually through all the threads to see if you can find what you need? aka...no search tools?? or did i just miss them?):confused:
I have a table that has country_code, Mfr_num, Item_num, catalog_code, mfr_obsolete_code. the key is on country_code, mfr_num, and item_num. I need to find duplicates of mfr_num and catalog_code. Can anyone point me in the right direction on this?
View 14 Replies View RelatedI have the data from a membership DB which has quiet a few duplicated Names and addresses (though they may have a 'type' field which is different)
I have identified these using the find duplicates wizard. Is it possible to use this query to delete the duplicated entries and leave one record.
Thanks
RussG