I recently got a new job and am trying to learn access for it. I have two tables. Both of them have client id numbers. They are both supposed to have the same client id numbers. However, Table 1 has more client id numbers than Table 2. I want to do a match query that selects the client id numbers in Table 1 that do not have a corresponding match in Table 2. How would I go about doing this?
Also, I want to do a simple select query where I select the client id numbers in Table 1 whose first two numbers are "88." How would I do this in the query or SQL form.
Do you have any recommendations about the best way to learn Access for practical applications like this? I'll also need to get good at making Forms which seem fairly complicated.
I'm new to Access. We have a database that was created years ago and has been working fine. Now suddendly we get the following error message on a query.
"The number of columns in the two selected tables or queries of a union query do not match"
This is the code
SELECT [TimeSheets All].[Job Number], [TimeSheets All].Date, [TimeSheets All].Details, [TimeSheets All].[Start Time], [TimeSheets All].[End Time], [TimeSheets All].[Unbillable hours], [TimeSheets All].Who, * FROM [TimeSheets All] WHERE ((([TimeSheets All].[Job Number]) Like [Forms]![Search]![Job Number])) ORDER BY [TimeSheets All].[Job Number], [TimeSheets All].Date;
I need to match the data in all the fields between 2 tables.
eg. Field 1 (Table 1) = Field 2 (Table 2) Field 2 (Table 1) = Field 2 (Table 2) so on......
Using the wizard, I'm only able to match 1 field at a time and this is very time consuming as I have 45 fields to match. Any advise? Can this be done via SQL?
Table 1 has National Insurance Number, first name, last name, phone no, address. Table 2: has National Insurance Number and email address.
Table 1 is the master table where it will link to table 2 containing the email addresses of the individuals.
However, there will be a lot of email addresses in table 2 which do not relate to any record in table 1 because I do not have the individual in my database yet.
BUT I want to keep their email address because in the future this person may enlist in my imaginary business and therefore - if they do I would have their email address!
1. Is this possible in Access?
In Excel, it would be the case of a simple vlookup from the email address field into another sheet containing the National Insurance Number and email address.
2. Would it be possible to establish a one-one relationship while enforcing referential integrity?
3. Is there a way I can establish a lookup which can be built into table 1 which can lookup the email address of a person in table 2, matching on National Insurance Numbers?
So in the future if new data is input into table 1 or 2 which results in a match of National Insurance ID numbers, the outcome would mean an email address is now paired to the corresponding individual it belongs to.
I have two access tables named as "DestructionTBL" and "AnnutiesTBL". Now I have to check each PolicyNumber of AnnutiesTBL with PolicyNumbers in "DestructionTBL". If match is found then add that PolicyNumber into "NODestructionTBL" and delete that PolicyNumber record from "DestructionTBL".
What I would like to do is for the BoatReg field in the Quotes Table to only display the boats that have been registered to the Client that has been selected, currently it displays all the BoatRegs.
I have done this in Excel before, but not Access. I do not know VBA. I figure this will have to be done in a query or a macro. I don't even know if all of this is possible in Access. I need to be able to split an Address field into:
Street Number Street Name Street Type Street Direction
And the purpose of this is so that I can pull out the Street Type (Drive, Road, Lane, etc) and update the abbreviations (DR, RD, LN) to the Street Type spelled completely out.
I did this in Excel by creating a Named Range "Types" on a sheet that has the abbreviations in Column A and the spelled out versions in Column B so that I could convert the abbreviations to complete street types. I broke down the entire address into each part on another sheet. Then I did a VLOOKUP to look up the Street Type in Column B in the Types range. Works great! And the only way I knew at the time to do that.
But, by doing this, I have to get the data I need from a download into a spreadsheet, break the address fields down on a second sheet, do all kinds of field update conversions (to get the field names from the download to match the field names in my Access table). This takes extensive Excel programming. I just thought there might be a way to do it by simply importing the data from the download straight into Access. That is easily done, but the Street Types aren't consistent.
There may even be a better way to do this than splitting....something like if a field CONTAINS DR update it to Drive. This would be a long process to set up because there are so many different street types to consider.
This is basically for the purpose of finding duplicates. If there is one entry called 123 Main St and another called 123 Main Street, they are not going to show up as duplicates, rather as two separate records.
Very new to access, I need an easy way to compare 2 tables with a common field (the name field) and list just the records that appear in the second table but not the first (primary) table.
Okay, this one should be simple and can be solved in one of two ways. I have two seperate tables with a 12 digit identifying number that is ALMOST exactly the same in each. I am trying to match up these two tables, which works amazingly well, except for the occasional case where the 12 digit ID (which is called API) ends in 01, instead of 00. The last two digits do not make a difference at all, but in one table it will end in 00 and the other it will end in 01, even though they are the same record.
Basically, it looks like this... Table 1---------------Table 2 541236554700-----541236554700 541236123700-----541236123700 443231246700-----443231246701
So basically, in my final query, the last entry will not show up since these two are not related by this API (ID) number. Any way to either replace the 01 on the end with 00... OR to remove the last two digits?
I have this table with this information...TABLE1code.............Name............Amount.....type'1000000'........'Name1'........... 0.00.....'N''1010000'........'Name2'........... 0.00.....'N''1010100'........'Name3'........... 10.00......'S''1010200'........'Name4'........... 5.00......'S''1010300'........'Name5'........... 0.00......'N''1010301'........'Name6'........... 1.00......'S''2000000'........'Name7'........... 0.00......'N''2010000'........'Name8'........... 0.00......'N''2010100'........'Name9'.......... 6.00......'S''2020000'........'Name10'......... 0.00......'N''2020100'........'Name11'.......... 3.00......'S'A need to create consult whose result show like this:RESULT'1000000'.....16.00'1010000'.....15.00'1010300'..... 1.00'2000000'......9.00'2020000'......3.00Could somebody help me this problem?. Thanks
I have a table customers and Purchases. Customer table has the {name, lastname, tele, address, city ..etc.} and the Purchases table has the {name, sku, unitprice, qty, cost}
Is there a way to create a report that shows the total amount of purchases made by customer?
Also is there a way to round up values in reports such that if the value is 3.95 it says 4.00?
This is going to be a simple one for someone, but it is proving to be a pain for me. I have two collums in a table, one that is a value another is a quantity, i have another field that totals the two fields. This works fine and shows the total but it does not store the total into the database, how can i get it to do this.
In the Total field the control source is "=[Cost]*[Quantity]" i have tried to set it too TotalCost=[Cost]*[Quantity] but this gives me errors
now when I try the section performing data entry with SQL, I am at the point where I have typed the SQL statement:
INSERT INTO Employees(EmployeeNumber, [Last Name], Gender, HourlySalary) VALUES ('227947','Jameson','M',18.85);
It chokes on the "unknown variable" HourlySalary, I could find nothing wrong with it.I then deleted my HourlySalary and copied theirs directly from the tutorial. It still chokes on HourlySalary.Now I am not sure what to do since I have taken their script and copied it to the SQL window.
Probably an easy one...I have two tables, each with a number field say "Cat-ID" and "Ref-ID". The query I am looking to write will show the outstanding values. For example
I have a table with two fields: Field1 Field2 S11.1 111001 S13 130001 S11 110001 S13.1 120001 I need to query to find where Field2's first three digits does not match the numbers in field1. Giving the sample: 120001 would be listed in the results because it does not match the numbers in S13.1 130001(2nd record) does because if there is no "."; it defaults to zero. I don't know to set up the criteria or what to ask so it would give me the results needed. Can anyone help please. Thanks
I have a form frmMember, it has a search button that performs a complex search of fields. after performing my search my [BaseDues] field shows #Name? BaseDues is set by a CBO in the field above.
It works correctly except after the search...
Here is the Code for the Search Button:
Private Sub cmdSearchForm_Click() On Error GoTo Err_cmdSearchForm_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmSearch"
I am writing an application for my personal use. I have 2 Access databases, one contains the data tables and the other has the forms, queries, reports, etc.
So far, I have been able to use the queries in the Row Source property of my form objects to accomplish what I want to do. Now I want to have a button that appends records to a table, the data in the table comes from several different controls on my form. After doing this the button should requery another control to display the new records added.
The OnClick event for the button allows me to use a Macro builder, Expression builder or Code builder. If I choose to use DAO in VBA code to append the records, do I have to define the database to create a database object to use even though the database is already open? Can I use a Macro to do this? Can a macro reference the data that is in the controls on my form?
I am trying to perform some keyword searches of a text field. My preferred option would be to hold all the keywords in a separate table and then use something like
SELECT * FROM Text Table, Keywords Table WHERE (((InStr[Text Table].[Text],[Keywords Table].[keywords])) > 0 ));
This works and returns anything which has the string from the keyword table, however I wanted to look for whole words only. I can do this in SQL using a regular expression along the lines of [!a-z] keyword [!a-z] which only finds the string where it has no letters directly either side of it.
What I would like to do is combine the two methods so I hold my keywords in a table and then use them with the reg ex to find whole words only.
how to do anything with it outside of tables and queries. I have been tasked with creating a form to do fairly basic things. Via tutorials I have managed to use the Expression Builder to do virtually everything I need except one thing.
I need to pull a value from row 2 in a specific column from a subform and subtract it from row 1 in the same column and subform leaving behind a + or - number for the user. Similarly, I need to do the same but subtracting the LAST ROW from row 1. If there is only one row at the time the operation is being performed, a value of No Data needs to be returned instead of a number.
I have converted an Old FileMaker Pro DB to Access. There is a record for each of the 22,000 sheets of engineering project documents issued over a period of years. One of the main fields we query by is the "Description" field. If I have the following Criteria: [Enter Document Description] Then save and exucute the query Access seems to look for an exact match to the value I provide. I need a query to return ANY record with the criteria value in it. For example If I entered "Fire" I would like to return anything with "Fire" in the description: B Building FIRE alarm Install Building C FIRE sprinkler Demolition A Building FIRE Exit Signage Site FIRE Protection Main etc. How do I phrase a query to return records that contain the value I provide within the text string in the "Description" field?
I realize this is a dumb, rookie qiestion. I tried Access Help and I tried a "search" in this Forum, I probably didn't do a good job describing my problem.
Greetings all! I am working on something and it has me stumped. Basically I have a list of Eastings and Northings and I am trying to find the closest postcode centroid based on the PAF file (for those that do not know of the PAF file, it contains a list of postcodes and the easting and northing of the centroid). The best way that I can see of doing this is: For each record I am trying to match, calculate the distance from the Easting,Northing to every easting,northing in the PAF. Select Min(Straight Line Distance) from the results.
However I do not know how to do this without matching each record that I am looking at individually! Can anyone give me an idea of how I can put this query together? I am using MS Access :(
Quick question… I am trying to accomplish something that I believe it is fairly simple. At least in Excel, it is. I have two tables with names. I am trying to create a formula that will compare name in table 1 with a name in table 2. If the names match (exact matches only) it will let me know. Something like =IF (NAME1=NAME2,”TRUE”,”FALSE”) in Excel. I have trying using Iif, but it returns that I cannot divide by zero. I have linked the tables, so if I query NAME 1 and NAME 2, it will only display names on table 1 that are found on table 2. Any help would be appreciated. Thanks,
Hi, I have two tables of data, one is a customer information (membersdata) table and the other is information recived from a bank (bankdata). Each customer has a 'bank description' field in its membersdata table and the bankdata table also has field 'bank description'. The query I have at the moment gives me back the data that both tables have a matching 'bank description'. The query I want is one that will give me the data from the bankdata table that does not exist in the membersdata table. So simply put the query I want is the opposite of the one I made with the wizard.
I have several tables that I need to join together to create a single form for multiple entries. Job Table, OPR Table, Organization, Program List Table, Program Notes, Rank, Reference Table, and Status Table.
Ultimately, I need to be able to display all of these in one form and allow for adding/editing notes from the Program Notes section. I also need to be able to let the user look up all info by selecting the Program Name and have the other field populate correctly. The issue I am running into is that I cannot get the Program Notes table to join to the Program List table correctly.
This is what I attempted to use in SQL: SELECT [Program Name], [Status], [Reference], [Self-Inspection], [IG Checklist], [Continuity Book], [Bragging Paper], [Program Strengths], [ORI Reports], [Best Practices] FROM [Program List Table] UNION SELECT [Log Date], [Log Entry] FROM [Program Notes]
However, I keep getting an error. I have read that it is because I don't have the same amount of columns, but the tables don't have the same information.I have also tried to do it through a normal query and through a third table, however I get errors about ambiguous outer joins and I can't seem to make that work either.
My boss wants me to program in visual basic, instead of using queries, a calculation between times, which values are gotten from a table. How do I perform a calculation between two fields from a table in code and what functions are there to calculate the amount of difference in minutes between times in visual basic? THANK YOU SO MUCH IN ADVANCE!
