General :: Two Tables - Search And Get Name Of Column
Nov 20, 2014
I have 2 Table that I will like to search in. I want to be able to search on a date and get the “ID” and the name of the column the date was found. Is that possible? I am thinking some kind of a Search-box, that will give a table/report.
The names of the tables are “2_PT_datoer” and “2_PT_CG_datoer”
In the first I want to search in the columns: “2U_PT-O_sendes(udfyldes_automatisk)”, “2U_PT-O_rykker_sendes(udfyldes_automatisk)”, ““4U_PT-O_sendes(udfyldes_automatisk)”, “4U_PT-O_rykker_sendes(udfyldes_automatisk)”, “8U_PT-O_sendes(udfyldes_automatisk)”, “8U_PT-O_rykker_sendes(udfyldes_automatisk)”, “6M_PT-O_sendes(udfyldes_automatisk)”, “6M_PT-O_rykker_sendes(udfyldes_automatisk)”.
In “2_PT_CG_datoer” I want to search in the columns: “Tjek_at_blodprove_er_taget_1(udfyldes_automatisk) ”, ”2U_CG-O_sendes(udfyldes_automatisk)” etc.
NB: I need an explanation for dummies as I am new to Access/Sql/codes in general
View Replies
ADVERTISEMENT
Sep 10, 2012
I've created a access database containing multiple tables, theres one main table that will contain all the data compiled.
I then have other tables that contain some matching data but with an additonal column filled in,
I want to be able to pull the data from these other tables into the main table and compile it including merging some data into a single feild.
Effectively this is what the inputs will be:
Code:
Table 1
Ident Number Solution TRN
T0941A1 SLN019149 EFE0008
T0941B1 SLN019149 EFE0008
T0941C1 SLN019149 EFE0008
Code:
Table 2
Ident Number Solution TRN
T0941A1 SLN019149 EFE0167
T0941B1 SLN019149 EFE0167
T0941C1 SLN019149 EFE0167
Code:
Table 3
Ident Number Solution TRN
T0941A1 SLN019149 EFE0188
T0941B1 SLN019149 EFE0188
T0941C1 SLN019149 EFE0188
Then the main table i want to compile the data like this:
Code:
Table 4
Ident Number Solution TRN
T0941A1 SLN019149 EFE0008,TRN0167,EFE0188
T0941B1 SLN019149 EFE0008,TRN0167,EFE0188
T0941C1 SLN019149 EFE0008,TRN0167,EFE0188
i want it to show as 1 line item and compile the TRN numbers into a single feild per row rather than having duplicates.
View 1 Replies
View Related
Sep 21, 2012
I'm having Table with some universities name and i want web link address for all universities. Take university from table1 in column1 and search on google page and return first link of the search page and save into column2...
View 1 Replies
View Related
Mar 10, 2008
I have a database that uses four forms and each form has it's own table and each form has it's own search button to find a specific record within that table. I would like to use one search button that will look at all four tables and bring up the correct record when the request number is entered. Can I link all the tables to perform this or is code required? Can someone send me in the right direction for how to do this? Give me an example of code if it's required?
Thanks for any help I can get.
Laura Edmark
View 7 Replies
View Related
Feb 9, 2014
I have to change a few column names in my Tables. I am looking for an easy way to check all the places where this column name is used. In VBA modules i can search for its usage, but is there a way to search if the column is used in a form in one of its fields or if its used in a control? At the moment the only way I know is to simple make the table column change and then used all the controls to see where I get errors.
View 3 Replies
View Related
Jun 16, 2014
I have added a combobox to one of the forms in my customer database using the "find a record in my form based on a value in my combobox" section of the combobox wizard.I have a number of columns in the combobox (the primary key is the bound value, and I have unbound columns for first name, surname etc).
I sorted the records in the combobox by column 3 (surname) so I can scroll through the list and choose surname instead of having to memorise customer numbers. Now I have more than 1500 records (with more on the way), scrolling through takes ages, and I was wondering if there is a way to adapt the combobox so I can (for example) type the first letter of a surname into the box and be taken to surnames beginning with that letter, whilst retaining the primary key as my bound column?
My initial thought was to base the combobox values on a query, and make the query prompt for a starting letter (or string of letters) each time it runs, so it only displays the query results in the list, but I was wondering if there is a neater way to do it?
View 2 Replies
View Related
Mar 6, 2013
I have a column that has a bunch of keywords they are separated by comma... so for each row of that column it will have a few different keywords example: lake superior, river, mountain, lake wollongong
I know its a bit of a nono with databases to have columns with comma separated text.. well so i read somewhere anyway but the document i have been handed to work has hundreds of rows in this column with up to 14 keyword entries.
I have a form that searches through Item's names based on 2 keyword boxes.
Here's the criteria in my query:
Is Null Or Like "*" & [Forms]![frmItemView]![SearchPhrase1] & "*" And Like "*" & [Forms]![frmItemView]![SearchPhrase2] & "*"
This will show all records when both keywords are blank, and filter records using the 2 keywords otherwise.
I have a form with two text boxes and have set the correct values as outlined in Evans post. I then have the query set to run via a button. I run it but it will only give back records for the entry i have put in the 1st text box. This would work well for me otherwise... mine is like this:
Is NULL or Like "*" & [Forms]![Searchtable]![Key1] & "*" And Like "*" & [Forms]![Searchtable]![Key2] & "*"
Searchtable being my search form
key1 being my first text box entry
key2 being my second text box entry
View 1 Replies
View Related
Jun 17, 2014
I'm opening a search form using DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria for user filtering purposes. I added a column which it now appears as the last column - I need it to be the 4th WHEN THE FORM OPENS. I can move after it opens, but it won't default there. Tried rebuilding the query and the form, but it still opens with this column at end of form - how can it be placed where I want it?
View 2 Replies
View Related
Nov 19, 2014
I have a form which has a combobox called Task_Ref which looks up values in a table column.
I would like to be able to set the tickbox value of tickbox called P1 to True if the combobox contains the word "test", each entry on the combobox selection may vary such as:-
Test number 1
Yesterdays Test
As long as the word "Test" appears I would like the above to happen?
I was thinking of something along the lines of:-
If InStr(Task_Ref.Value, "Test") > 0 Then
P1.Value = True
Else
P1.Value = False
End If
End Sub
But this hasn't worked
View 4 Replies
View Related
May 29, 2015
i would like to sum all the column fields in a tables column where an id is the same as the id in that table.
I know you can use the SUM function in the select statement of your query.
But how can i set this sum result into a variable, so i can use it for calculation further on in the program?
I work with Access 2010
View 4 Replies
View Related
Nov 12, 2013
I am trying to add a value to a column in a query based on the value of another column.
I am using an iif statement for it but can't get it to work. I have a column called [Was Worker Born in UK] which has 3 options of "Yes, "No" and "Unknown". The next column is the [COUNTRYOFBIRTH] column.
I basically want [COUNTRYOFBIRTH] to say "866" if [Was Worker Born in UK] is "Yes".
View 14 Replies
View Related
Jun 5, 2014
I have a list box with 3 columns and one line on my form and am writing an update query that is to use the value from the first column of the query to update a record in a table. I have referenced the list box as ListBox.Column(0) but the code displays a value of null when I run it and the record that is to be updated is updated to Null (it is blank). How do I write the code so that the value that is in column 0 of this list box is passed through the code?
View 14 Replies
View Related
Aug 5, 2015
I have a table that has a column of Equipment Numbers (e.g. CN330) and another column with corresponding costs. The Equipment Numbers are not unique. A number may appear many times in the column and the cost may be different each time. (The source data for this is an Excel spreadsheet provided by another department).
I need the totals for each unique Equipment Number but not in a "messy" format like subtotals. I need to use it as a lookup for another part of my database so want just two columns; unique Equipment Numbers in the first column and the total cost for each in the next column.
I have the unique Equipment Numbers in the first column of a new table but cant figure out how to get the total costs.
View 2 Replies
View Related
Jul 1, 2013
I'm currently running a replicated database in Access 2003 (plan on migrating to 2010; but have several users on 2003). I guess I pushed the number a fields within a table to be close to the 255 max and of course had a conflict in that table which put me over the top. Now if I receive the following error: "Cannot add a new column to conflict table 'Table_conflict'. Delete obsolete columns and compat the database."Since this will occur with all of my replicas is there a way do either automatically clear the data in the 'Table_conflict' or remove the table?
View 3 Replies
View Related
Jun 10, 2015
I just started my internship a couple weeks ago so I am still relatively new, but here's my problem...I have created a query to run the weekly statistics for my data but one column continues to show multiple times. I want the "Left in Estock" and "Left in Estock Line Items" to only show once, instead of all the way down the side. Check out the attachments.
View 11 Replies
View Related
Feb 19, 2014
I have 2 tables:
Trainers
-> Name
-> Date of Birth
-> Address
etc etc.
Courses
-> Name of Course
-> Course approval expiry date
-> Type
etc. etc.
I now need to make a table whereby I can have the list of trainers and tick the courses that they are allowed to teach in each column.
I can forsee the columns are being:
-> Trainer
-> Course 1, course 2, course 3, course 4 etc etc.
The problem is that courses can be added/changed/deleted.
View 10 Replies
View Related
Jul 4, 2013
How do I add a sum row of the column in access 02 at the end of the column?
View 4 Replies
View Related
Jul 11, 2013
I am looking to add 3 or more different list values into the same column on the same table.All lists are stored in seperate tables within the same database.
View 6 Replies
View Related
Aug 7, 2012
i am trying to clean up all unneccessary queries. i am now trying to get a calculation to apear in a text box.
what i want to calculate is [tblReceipts].[ReceiptCash] If Date()= [tblReceipts].[DateOfReceipt]
am i going about this the wrong way of is my pricipal correct but my expression wrong?
View 13 Replies
View Related
Jun 7, 2012
I have a table with a number of columns, one is "Date Due".
Any date in "Date Due" that is less than the current month, I want to colour Blue.
IF this was Excel I can do it a number of ways, and are proficient in VBA, but when it comes to Access !@#$%*
Tried to use Conditional Formatting, shows up on tool bar -Grayed out.
View 1 Replies
View Related
Jun 12, 2015
I am using access 2010. I have "classlevel" table with 2 columns-Class and Value1 .Value1 column has numeric values that i ll input from webpage (webpage to ms access connectivity).
I want to sum the values of column "Value1" and i have another table-"Volume" which has 2 columns "VolumeLevel" and "Value2". So i want to match the sum that i calculated from first table-"ClassLevel" with the "value2" column in "Volume" table and get the corresponding "volumelevel" column value from that table and there is a third table that will get this volumelevel value.
There is no common column to join these tables.
Classlevel-
Class Value1
Class 0 3000
Class 1 2000
Class 2 300
Class 3 400
Class 4 500
Volume-
Volumnlevel Value2
Vo 0
V1 5000
V2 10000
V3 30000
....upto V10
Answer that i am looking for is V1 as sum=6200 lies between 5000 and 10000 so its V1.
Table 3-
Volume-
V1
View 14 Replies
View Related
Mar 26, 2015
I would like to get minimal value from "Ski" column. Need to use recordset instead Dmin function because this value will be changed many times. I work with dao.recordset but I never used recordset filter so I need to do something like this:
Code:
strSQL = "select tblGoraZleceniaNowaWyceny.id_gora_zlecenia, tblGoraZleceniaNowaWyceny.naklad_pracy, tblGoraZleceniaNowaWyceny.nazwa, tblGoraZleceniaNowaWyceny.Ski, tblMontazSzczegoly.iloscuzytkow from tblGoraZleceniaNowaWyceny inner join tblMontazSzczegoly on tblGoraZleceniaNowaWyceny.id_gora_zlecenia = tblMontazSzczegoly.nazwa where tblGoraZleceniaNowaWyceny.id_wycena_pre=" & Forms!frmWycenyObszarRoboczy!ID_wycena_pre & " order by error desc"
Wor = 5
Get the minimum value from strSQL (Ski column)
update this value = Ski+1
wor = wor-1
and loop all procedure until Wor = 0
So the problem is how to requery strSQL to show NEW minimum value at the beginning query - rst.requery doesn't work.
View 4 Replies
View Related
Jul 18, 2013
Here's my dilemma: I have a table that keeps track of job postings and how many people applied for each posting. So, for instance, I can have 20 people apply for a job posting say....on July 18. On another column I have the shift name for that posting such as DAY Shift. So, I have 20 people that have applied for the Day Shift on July 18.
What I'd like my combobox to show me in just one instance of July 18 in column 1 and one instance of Day Shift in column two. I have tried using the wizard and then going to the properties of the combobox and attempted to edit the SQL statement to use the DISTINCT value to no avail.
View 14 Replies
View Related
Jul 26, 2007
I was just assigned this database task couple weeeks ago, I know VERY LITTLE. I have all my tables and forms set up. My company builds custom cars....very few per year. I have dozens of tables for all the different componetns of the car....and each table has the same column "Unit" in it. I have a form that when a new car is began being built, the the first thing it does is ask the user to type in the Unit for the new car, but that only puts it in the one table i referenced when creating this form. I want/need this Unit information to be placed in every single table (20+) that has the column "Unit" in it. How can i do this, any information is greatly helped.....im getting so frusturated, i feel like i've learned a lot in the past 2 weeks since starting but i stil really know hardly anything.
View 3 Replies
View Related
Aug 13, 2015
In query, I would like to compare two tables and get the result but I always get the round robin result.
Table1
OrderQty Company Order# 8 Oop BBS 12 abc AAA 3 abc AAA
table 2
OrderQty Company Order# 7 Oop BBS 12 abc AAA 3 abc AAA
Result I get which I don't want What I want is the last table
Order2.OrderQty Order2.Company Order# Order1.OrderQty Order1.Company 3 abc AAA 3 abc 3 abc AAA 12 abc 7 Oop BBS 8 Oop 12 abc AAA 3 abc 12 abc AAA 12 abc
The result I need. How to get this?
Order2#Company Order# Order1#OrderQty Order1#Company 3 abc AAA 3 7 Oop BBS 8 12 abc AAA 12
View 4 Replies
View Related
May 19, 2014
I have a table with columns as
1- Name
2-Marks in Subject 1
3-Marks in Subject 2
4-Marks in Subject 3
I want to create more columns in same table as
5-Total Obtained Marks
6-Maximum Marks
7-%age of Obtained Marks
How can I do it in one table
View 3 Replies
View Related
