I want to be able to merge the two records together if the field chr matches between the two files plus if there is an overlap between the start number and end number from each file. For instance the first record from each file would match because the range from 1000 to 2000 of file 1 has numbers consisting of 500 numbers (1500-2000) that are also present and overlap in file 2 (1500-3000). I possibly cannot use < or > since the ranges from each file will vary to different degrees. Perhaps there is a between function that might work...
I have a few tables containing different exam data for students - and a unique identifier with each. I need to add another unique identifier (which is already specified from another system).
Now, can I use the existing identifier (UPN) to automatically put in the new identifier into a new field (AdNo).
If that makes any sense at all, I will be surprised...thank you!
I have a database with two tables, Buyer and Products, with one-to-many relationship. The key of Buyer is an autonumber ID, which is the foreign key to table Products.
The problem is that i have two copies of this database with different records (with ID starting from 1 to each one) and i want to merge the databases into one.
Any ideas how to do this without lose the connection of the records?
How do I merge these two datbases? I have downloaded them from Microsoft online templates, here http://office.microsoft.com/en-gb/templates/TC010178391033.aspx?CategoryID=CT101426031033&av=ZAC000 and here http://office.microsoft.com/en-gb/templates/TC010185481033.aspx?CategoryID=CT101426031033&av=ZAC000 and I want to have the two databases as one so that there will be a fourth tab on the cintact management database with the order form on? I want to be able to link the two so I can have all the customers details and orders together and print off reports. I really have not dealt with Access before so I really dont have a clue. Any help would be truly appreciated. I am sure it is easy but I just cant work out how to do it.
I am trying to aput a stock number into a form called (products) but it tells me there is no matching number in another table ( stocklist). yet that number is in another table (stocklist.
I have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error
Private Sub Command5_Click() If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then MsgBox "You welcome" Else MsgBox "Wrong username or password" End If End Sub
I have to coordinate a dbase in the US, one in the UK, and one in the KSA. I have multiple documents that may, or may not, be in each of the three dbases. I need to merge all three, and maintain them so that the document additions, as well as any changes associated with that document, (i.e. "revisions"), are updated. Having looked at access and having read through some of the Access forum questions, I understand that it can do that, but after having identified the information from the dbases that will go into access, I'm trying to establish a consistant process for maintaining and updating it. Can I export my information from each of the dbases, into an excel spreasheet, (1 for US, 1 for UK, and 1 for SA), and have the access dbase search for the document number, compare the data following the document number, and if it's the same ignore it, and if it's different, update it?
I'm trying to muddle through this and get access and excel to do as much of this as possible, as it's a very tedious and time consuming process to manually check each one, one or two times a week.
I'm trying to make it a little further with my new call answering database at work.
Basically it consists of a main form that has a button leading to a call answering form for each business that we represent. it also has a text box that displays the number of the caller when the telephone picks up, the correct call answering screen is also popped by the telephone software and a macro whenever the phone is answered.
I've got code in place that automatically puts the date and time in the correct field and also copy the incoming telephone number from the main form into the caller number field on the call answering form.
I'd like to take this automation a little further now and get the form to autopopulate the caller details if that caller has called before (we get a lot of calls from the same people) so i'd like to make the form search the table it's linked to for the incoming phone number and to fill in the name, email, company etc... for the caller according to the previous record.
The code for the 2 forms i've currently got setup (the switchboard and one call answering screen) are as follows
Switchboard:-
Code: Option Compare Database Dim WithEvents MaxxCom As Metro_MaxxCom_CTI_COM_API.CTI Private Sub cmd_onnet_Click() DoCmd.OpenForm FormName:="On_Net_Communications" End Sub
I have a subform in which I want to put in a Command Button to open another form.
I used the wizard as per normal, but when you come to matching specific data fields, there is nothing in my left hand column of the sub form. I have checked the record source property and it is bound to the correct table.
I have read that this is an issue with Access 2007? Is this the case or am I missing something obvious here?
I have database for each location. Every location will update some information in the databases and send it back to me. In those location based databases, information will be stored in one table called "consolidate_input". I will store these databases in one specific directory and then I want to accommodate the data of "consolidate_input" into one master database having the same identical table in the database.
I have started working on an existing MS access application (Front-end & Back-end) built with VBA. The frontend linked to a back-end access database which contains only tables. Because of the nature of the work involving vehicles. Each user has their own copy of identical databases with their individual data stored in backend.
Right now we just copy each back-end database via ftp and store it as usr_backenddb_date.accdb Back end databases contain around 16 tables with most of them containing a autonumber field as a primary key. Further tables are connected with each other referencing the primary key as a foreign key .
I would like ideally create a function in vba that lets me select the database and merge all the data from that database to a identical central database. For the initial part, I am thinking of combining each individual table in each database and changing the autonumber field with SQL with possibly trying to cascade the change so the refrences remain intact.
Constant Access to one back end db via internet (or server) is not really possible as the users are able to connect to internet (network) only few times a day. There are over 100 backends (for each vehicle) with over 1000 records in each.
I have created a rather large data entry form for one of our departments which will be used to run a mail merge document. The merge is run off of a query of the form, rather than the tables themselves...I think that is what I've read is the right thing to do?
Anyways, most of it is working great so far but I've run into an issue where a few of the form fields are combo boxes. In access and in the query the data looks correct, but when you look at it in word (in edit recipient list) and after the merge, it will have a file path name instead.
So a combo box that has "Medical Plans and Visual Plans" in that field in the query, comes over saying "c:Users ameAppData
Ex: New Hires are provided information about Medical Plans and Visual Plans. Ex: New Hires are provided information about MeC:UsersbrooksAppData
It always shows the first two letters before putting the filepath name in there.
I have tried doing it multiple ways...a lookup to another table, lookup right in the field itself....get the same results.
The fields we want to focus on is "AccNo" and "TestCode"
Now i want to filter based on this condition:
If "AccNo" AND "TestCode" BOTH have duplicate data in a record. Show that record only once. Remember. . . "AccNo and TestCode, both these fields have to have duplicate data in a record.
Example: MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc CAPT.....M566679.....John Blue.....123456.....CBCA CAPT.....M566679.....John Blue.....123456.....CBCA the example above should only display once because of the repeated AccNo and TestCode
Example: MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc CAPT.....M566679.....John Blue.....123456..... CBCA CAPT.....M566679.....John Blue.....123456..... LIPID the example above is just fine because the TestCode is not matching
How do i create a query to accomplish this? someone please help, i've had troube with this for the passed couple weeks . .. .
I have not found the solution to this looking elsewhere, but basically what I am trying to do is the following:
person date 1 1/1/2012 1 12/15/2012 2 6/12/2012 1 6/13/2012 2 6/14/2012 3 6/16/2012
I want a query which shows the count or records for which a person has a date within 30 days of each other. This is being queried from a large table in which records would may not be sequential by person. Thus in my example above, the third record for person 1 would be omitted as well as the record for person 3.
I often create contract using mail merge. I have an access file that I want to use as data source for word file. But it does not automatically.
Please download the attached file !
If there are 1 customer and 1 property, I do not need to do anything. Conversely, if there are many customers and many properties, I take time to manipulate.
Firstly, I open the word file. I have to copy and paste paragraphs that I want. Highlight of the original paragraphs is blue.
Secondly, I click 'Insert Word Field' -> select 'Next Record'.
In short, I wish to use VBA in access file to automatically perform the steps that I have outlined.
Wondering if you can help I have a table called "example" which has field "a" "b" "c" "d"...
I then create another table called "importtable" with field "a" (imported in from Excel)...
I would then like to create a query which matches any and shows all the records which I imported in from Excel to my current table called "example" I looked at joining via "relationships" using a Select Query but it doesn't quite show what I'm after...
Should add I would like Field "a" in both tables to show but only if the number exists in the "importtable" if not then don't show..
In a database I have two tables, one is linked to an excel sheet (our customers order) and the other is created via a "make-table query" from our business system.
In both tables I have the customers part numbers and neither contain a primary key.
What I need to do is compare the part numbers in both tables to find if a part number is present in the order but not in our business system.
I have written a VB.net application that uses an Access database. I have a table that displays the following information.
Customer Name Customer Phone Customer Address Shipping status
Now in my vb.net application I am displaying these 4 things in a data grid. But here is the problem. The shipping team updates their database daily with the new Shipping status. So to get the latest shipping status I created a linked table, lets call is link_shipping. Now All I have to do is somehow get the shipping status field from link_shipping and update the respective table in my database with this info.
To do this I created a query. but now I am having to manually go into my database and run this query to make sure the shipping dates sync up. How can I do this in a better way. The only thing I can think of is to find some way to automate the running of this query every day. But I dont know how to do this. Can anyone suggest anything here.
I have a series of numbers in 30 columns. I want to identify where either of two numbers appear in the same record regardless of which of the 30 columns they are in.
TEAMS. This contains TeamID / TeamName and TeamManagerName. TeamAPPLinks. This is a many to many relationship and contains TeamAppID / AppID and TeamID (TeamID links to Teams.TeamID and AppID links to Applications/AppIS) Applications. This contains AppID / AppName / Pass/Fail.
So teams can have many applications and applications can have many teams.
I only want to show teams that have all applications that have the Pass/Fail column as PASS. I have managed to do this for FAIL runing a query in a query but obviously its different for PASS as all associated applications have to be a PASS, not just one FAIL for a FAIL.
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID 1 5 2 4 3 6 4 2 5 1 6 8
Results records I'm looking to display for would be: 1 5 2 4 3 6 6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.
I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).
I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.
I'm trying to clean up a database and make sure that I don't make an error in future entrys.
Is it possible to make a "key" that highlights the rows with matching keys. The thing is that a the "key" is Switchname + Switchport, data placed in 2 different columns in the same row.
