Building A Query From Table Fields
Dec 2, 2006
Hi,
I am trying to make a query from fields out of 3 tables.
All tables must include following fields:
Table 1 fields:
WR04 (year 2004)
Date
Reporting Person
Table 2 fields:
WR05
Date
Reporting Person
Table 3 fields:
WR06
Date
Reporting Person
I am trying to pull together in the query any given individual (Reporting Person) who may be included in all above tables but believe I may have a relationship problem as I am only getting results that match all tables.
I hope this is clear.
Any suggestions please.
View Replies
ADVERTISEMENT
Sep 23, 2006
I've been trying to figure this out, but it looks like a no go.
I wanted to give my users the ability to create/modify queries, but NOT create/modify tables.
Does anyone know if this is possible in Access 2002?
Thanks,
Earl
View 2 Replies
View Related
Mar 7, 2005
Hello.
I'm building a history table to keep track of some changes that occur on one of my forms.
Here is what I'm using to build my history table:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblDateCycleTestingChanges", dbOpenDynaset)
With rs
.AddNew
![CycleTestingDatesID] = Me.CycleTestingDatesID.Value
![Changed] = Now()
![ClientID] = Me.ClientID.Value
![ProjectID] = Me.ProjectID.Value
![TaskID] = Me.TaskID.Value
![TaskStartDt] = Me.TaskStartDt.Value
![TaskEndDt] = Me.TaskEndDt.Value
.Update
End With
Set rs = Nothing
End If
I have 2 questions:
1. I'm not sure where to insert this event. I'm thinking on Before_Update on the updated field? (I don't want to put it into Before_Update for a form event as I have other things that are being filled out/changed and I only want to keep track if certain fields on the forms are updated/changed.
2. I would also like to keep track of Old and New values for those specific fields. Is that at all possible?
Thanks.
View 5 Replies
View Related
Nov 29, 2006
:( Hi Guys,
I have a very basic DB of customers names and addresses. What I want to do is find all the enquiries within the months of August, September and October, send all these enquiries to a new table so that I can use this new table for a mailshot.
The date of the incoming enquiry is in a field on it own and written as dd/mm/2006.
If there is an easy way to create a mailing list from the original table please let me know.
Best Regards
Keith:o
View 1 Replies
View Related
Mar 4, 2005
i have two tables that only need to exist for the length of the user's session. i build them with ADOX in my backend database when the user starts up, and destroy them with ADOX when the user closes the application. the name of the table created varies, too, depending on the user (userID is built into table name).
i need combo boxes on my forms in the front end to be able to access this data.
is there a way in code, once the temp tables are built, to create a 'link tables' type of setup?
my alternative is to build the temp tables in the front end portion, but i don't really want to do that.....
View 4 Replies
View Related
Oct 8, 2007
I have a table that lists county names. On the data entry form only the county names are displayed. However, I have another form that is used to build a custom query based on various criteria, of which county is one.
To make the table compatible with both both forms, the data source for the dropdown list for data entry is: Select * FROM county WHERE county <> "No Selection"
For the SQL form the code is simply: Select * FROM county The default value of the dropdown list is "No Selection". When dropdown list for county is set to "No Selection" the program interprets it as "select all counties". Other dropdown lists provide other parameters such as the project year, nature of the project etc. Each of these other dropdown lists also have a default value of "No Selection". The SQL form thus gives me the ability to mix and match several criteria. For example, it will display all projects in a particular year for a particular county or all projects in all counties that involve the construction of a residence.
My question, instead of physically having the string "No Selection" in the table itself, is there a way to embed the phrase "No Selection" in the SQL expression itself and still have it as an option on the dropdown list?
View 1 Replies
View Related
Jun 20, 2005
I have at least 15000 records and all of them should be corrected if there is existing duplicates with same EMBG and different name. More precisely if there are 2 persons with same EBMG lets say 123456789 but one with name Naim Arifi and other one Naum Arifi then query should present to me Naim and Naum. Example
1. Naim Arifi 123456789
2 Elton John 123456452
3. Naum Arifi 123456789
4. Naim Arifi 123456789
* *
* *
* *
* *
722. Naim Arifski 321546798
* *
* *
So the record 1 3 and 4 and 722 should be highlited because they have same EMBG and I need to find where is the mistake manualy (correcting from Naum to Naim). In this case row number 4 is mistake instead of Naim is Naum. I need to correct it manualy.
Could someone provide me some help
View 4 Replies
View Related
Nov 8, 2004
Ok, I currently have a query built with requests. What I want to do is initially set each request with a priority.
Then when one request is closed the query will take the requests with priority 2 and change it to priority 1, change the request with priority 3 to priority 2 and so on. Also any new requests that are entered I want to be able to set their priority as well. Can anyone help me with this, or is it even possible?
Any help would be greatly appreciated!
View 4 Replies
View Related
Sep 23, 2014
I'm making a library database program thing... There's an option for the user to view all books on loan.
I have two tables:
Books, which has columns ID*, ISBN, Author, Title, Year, Location
BorrowerStorage, which has columns Book ID, Name, Email Address, Desk Number
Book ID in BorrowerStorage is related to the Books primary key.
Now, for the viewing all books on loan, I want it to produce a read only table which contains all the entries from the BorrowerStorage table and the corresponding Title/Author columns (i.e. the records for which the ID in Books column = BookID in Borrower Storage column)...
View 5 Replies
View Related
Sep 20, 2005
OK, here's the deal. I have two data tables, one of which holds many types of customer information and has a very large number of records, including, name, address, phone, and email. The other table contains only email addresses. Each email in the second, smaller table corresponds to a record in the larger list , by virtue of both records sharing the same email address (primary key?). I need to be able to take a short list of email addresses from one table and produce all of the records from the larger table that have corresponding email addys, essentially. Can somebody shed some light on how I can structure a query to solve this dilemma? Sorry for the newb question, just getting started out using db's.. Thanks!
View 1 Replies
View Related
Feb 14, 2008
Hi
I am really at the end of my teather with this problem so i really hope someone here can find a solution.
I have 2 tables; Client (Client general info, defined by their location), ClientHardware (Info on the hardware a client has and also it's condition).
I need to be able to select one or more clients and display one or more conditions of their hardware, e.g. London, York, Bury + Red, Amber, Green condition.
After the Query is working right i will need to output it to a report through a button on the form.
I attempted adapting This Method (http://www.databasedev.co.uk/query_using_listbox.html) but adding another list and query just resulted in the report showing the all the records of the selected client (e.g. london) then all the records with the selected condition (e.g. bad) it would be ok if i could merge the list box selections into one query but right now it looks like this
MyDB.QueryDefs.Delete "qry ClientName"
Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL)
MyDB.QueryDefs.Delete "qry RAGType"
Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL2)
Is it possible to put the variables in strSQL2 into the creation of qry ClientName somehow?
Any help, large or small will be appreciated as I'm really at a dead end with this.
Regards
Phil
View 9 Replies
View Related
Jul 8, 2014
I currently have 2 tables in a database that I'm trying to build a query off of. The first table lists personnel & their position #s, and the second table has a column for "rated person", "rater", "senior rater" and "reviewer" which are all part of our annual evaluations. I'm trying to program the query to display the names of the person attached to a position #, but can only get the query to return the actual position #s.
I'm using the table to assign each position # appropriately, i.e. position # 10202 is rated by 10103 and senior rated by 10101; I do this with the lookup data type. Once each position # is assigned, I'd like the query to display the name of the personnel instead of the position #, i.e. when queried, it would return under the rater column JOHN SMITH instead of 10202, and in the senior rater column JOHN JONES instead of 10103, etc.
View 1 Replies
View Related
Oct 14, 2014
I have a combobox that I use to combine the employees first name and id number. So when you pull down the list you can select the employee you want and it is saved in the form. Now I need to create a query so that I can create a report on each employee separately. The query will not let me get the Employee Frist Name/ID from that saved field. I thought that what is saves in that field, you can retrieve it in your queries or reports.
View 3 Replies
View Related
May 4, 2014
I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).
As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.
View 1 Replies
View Related
Nov 21, 2014
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
View 2 Replies
View Related
Jun 6, 2006
My table looks like this:
Sales(a)||Date(a)||Sales(b)||Date(b)||Sales(c)||Da te(c)
1) 50 05/06 75 06/15 100 08/15
So I want to show all sales with the criteria - Date >05/01 <07/01.
The final query totals will be
Sales||Date
50 05/06
7506/15
So the issue is that there are 38 rows with similar data in each column
I have the table in this format because I also have a form that feeds off this table and it needs to be in this format-but I would also like to run a query/report from this table that summaries sales. I also know that since there is more than 1 date value in the row, this is going to make the query difficult to run (each date is linked to a certain sales total).
I guess what I want to do is transfer the horizontal data to vertical data (with certain criteria)
Please help!!!
View 1 Replies
View Related
Sep 12, 2014
In query design view, I have added a query with column (nameA) of names and a table with a column (nameB) of names. I am linking these two fields (nameA and nameB) which contain names. The names are spelled exactly the same so I should get results. Both fields are of the same data type.
But even when I add only the field with names from the query (the field called nameA), I don't get any results.
What is wrong here? Are there restrictions about linking a query to a table?
View 3 Replies
View Related
Sep 3, 2015
I am a student and I've just started to work with the MS Access and databases.
I am wondering if it is possible to filter the fields of table and display only specific information from that specific field. For example: The database (*.mdb file) is created automatically by the software (EPLAN Electric).
There are columns with fields like "de_DE@Verbindungsleitung;pl_PL@Kabel laczacy;en_EN@Connecting cable;en_US@Connecting cable;" But I would like to display in query/table filed only text starting from "en_EN@*" or "??_??@*" or display nothing...
I know that it is possible to display only rows which contain this text, but how can I get rid of fields from unwanted words...?
I cannot change original table of database because the parent-program (EPLAN Electric) will not recognize this database... And additionally data base is updated via EPLAN so every new field should be filtered in this way...
View 9 Replies
View Related
Oct 4, 2007
If I have a make table query where I want to add in some blank fields, say: Field1: “” , Field2: “”, etc…. is there a way I can make these fields a yes/no type instead of the default text?
I know I can manually go into the table in design view after I run the query, but I was hoping I could do it beforehand. :)
Thanks!
View 1 Replies
View Related
Apr 3, 2005
I have two tables:
Employee
TokenID (PK)
name
location
post
..
..etc
Rating
TokenID (PFK)
FinalScore
I am trying to display all the fields from Employee table where Employee.TokenID= Rating.TokenID but I only want to display the fields from the Employee Table and not from the rating table. And each record displayed should be distinct by the TokenID....
View 5 Replies
View Related
Jun 15, 2006
I am using a table a user created which is like:
Member ID (key field)
Visit Date
Dept 1
Expense Code1 (combo box E through I)
Dept 2
Expense Code2
This goes on through Department 20.
Now they want to know how many E's for one month. I am stumped on how to normalize this or if it is even possible! I thought maybe there is a way to search the table as if it were a spreadsheet doing a "countif" function??
Any replies much appreciated!
View 1 Replies
View Related
Sep 18, 2013
I am developing a Make Table Query from 2 tables, one of which has an "Employee Name" field (lastname,firstname) and the other table has separate fields for LastName and FirstName. I've been able to accomplish almost what I need by:
WHERE ((([Table 1].[EMPLOYEE NAME]) Like [Table 2].[LAST NAME] & "*"));
Which works fine except when there are 2 employees having the same last name, then it generates duplicates. I suspect there must be a way to do this by incorporating the first name field in the sql statement but that's beyond my ability. I realize that names are not good things to base a query on, but the 2 existing tables have been preset and populated by others and I don't really have the capability to change them.
View 3 Replies
View Related
Jan 13, 2014
I am looking for a way to generate a list of all fields within a table, have the end-user select which fields he/she wants to include, and then run the query. I am trying to create this within a form for a nice, easy to use GUI.
I am using Access 2010 on Windows. The fields I need them to select from are in one table, however there are many lookup (tblkp) tables related.
View 2 Replies
View Related
Aug 15, 2014
I've been developing an Access database to keep track of my company's ongoing projects. There's also a form to browse the history of users actions within the program. It's based on table tbHistory that stores actions and parameters as numbers (for example eventId = 1 is "logged in" and eventId = 2 is "logged out"). I've been using a query to translate those numbers to text with a syntax like:
Code:
IIf([tbEvents].[EventId]=1 Or [tbEvents].[EventId]=2;[tbEvents].[EventDesc];IIf([tbEvents].[EventId]=5 Or [tbEvents].[EventId]=6;...
It worked fine but eventually the expression within expression builder has grown to the level that exceeded allowed limit and I couldn't develop my statement any more.. I decided to develop a vba function that would take eventId as a parameter and would retrieve a string, here it is:
Code:
Public Function translateHistory(eventId As Long) As String
Select Case eventId
Case 1 To 2
translateHistory = ""[tbEvents].[EventDesc]""
Case 6
translateHistory = "[tbEvents].[EventDesc] & "" <b>"" & [tbFormDesc].[FormName] & ""</b>"""
[Code] ....
And in my query I replaced that extremely long expression with just translateHistory([tbHistory].[eventId]). It seems to work, but the result it brings is a pure string with table names and fields - in other words, the query doesn't recognize it should be replaced with appropriate value. Here's the output I get:
Of course I'd like "[tbEvents].[EventDesc]" to be replaced with appropriate value of field "EventDesc" from table "tbEvents" as it used to be.
View 13 Replies
View Related
Aug 31, 2015
Table 1: contains sales summed by salesrep by week_date for 5 years + current
Table 2: contains sales rep, start_date and end_date
Query: Sum sales by rep where (start_date >= week_date and end_date <=week_date)
Each rep has different start and end dates.
View 7 Replies
View Related
Mar 23, 2012
I am using Access 2007. I have an ODBC source feeding data into a table, to which I made a crosstab query that displays the useful data from that source. I need a way to add data(new field), using a form, that is a associated with the lines in the query. I've tried creating another table that uses the sample ID as the primary key, but I need it to automatically create a new row for each query line. Is there a way to do this, or better way to add data to a query. Examples are below.
The ODBC data looks something like this
SampleID PeakName Conc
sample1 THF 50
sample1 MEOH 10
sample1 propanol 25
sample1 butanol 15
sample2 THF 21
sample2 MEOH 15
sample2 propanol 17
sample2 butanol 23
I have the query look like:
SampleID THF MEOH propanol butanol (I need to be able to add a field here)
sample1 50 10 25 15
sample2 21 15 17 23
Linked table I created:
SampleID LIMS #
sample1 1234567
sample2 4567891
The problem is, for this table I have to manually select each sample ID. I want it to automatically add the sample IDs as new ones are querried.
View 3 Replies
View Related
