I had to retrieve data by grouping records based on f1 value
and i had to display f3 value as "-" if all f3 values for a f1 are distinct.
If all f3 values contains the same value then i should display that value.
I should get output like this
f1 f3
1 -
2 DD
I tried a lot by taking the distinct count of the records and displaying - when count is > 1
Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.
I have a combo box that I want to display titles of discussion that fall within a date range. I set up the query as such;
SELECT DISTINCT Discussions.Discussion_Title, Discussions.Discussion_Date FROM Discussions WHERE (((Discussions.Discussion_Date) Between ([forms]![DiscussionsForm2]![txtStartDate]) And ([forms]![DiscussionsForm2]![txtEndDate]))) ORDER BY Discussions.Discussion_Title;
It works but not quite the way I want it too. I would like the combobox to display only unique titles but right now it shows the same title if it was used on another date. I guess its showing distinct titles and dates, while I want it to display just distinct titles.
I am a novice and have been searching to no avail for a solution to my problem. I have a main form and a subform (datasheet view) of a table. In my main form I have various calculations which calculates the data from the subform (when auto-filtered). What I am trying to do is count the distinct [Call Number] reflecting the data from the subform into a textbox in the main form (when subform is auto-filtered).
I am currently trying to make a form which will generate a report for the users. Currently the form has 3 combo boxes where users select which fields they want displayed from the table in the report. They also have sorting options next to them (Asc or Desc). I am now trying to put in a multi select list box under each combo box to allow users to filter the report to their liking. Currently I am trying to pull distinct values from the specific field of the table and display them in the listbox for users to select.
I have tried using vba to set me.filter1.rowsource = SELECT DISTINCT me.combo1 FROM EVAP_Database, but this doesnt seem to work. I have also tried a few other codes and still no luck.
hi guys, i have a table that stores information about packages
[autonumber],[pack number], [county], [uber number] ( the uber number is made up of the pack number and the county ), etc
and i have a history table so that when anything is done with a package it is recorded in the history.
[autonumber],[uber number],[event],[date],[user]
they are linked by the [uber number] with a one to many relationship so that is shows all the events for a specific pack.
but now i need to create a list of all the packs that are at certain events
but if i just create a query with the packs info and the event number from the history i get all the packs that have been at that state and not just the packs that are at that state.
Table 1: Students that are registered this semester. The unique Identifier = full 9 digits of SS#'s
Table 2: Students and regular people current working for the college. Unique identifer = only the last 4 digits of their SS#'s
Objective: I need to know what registerd students or regular people are currently working in the college. In other words, compare table 1 and table 2. The problem is I do not have a common unique identifier in both tables.
Additional Info: Each table also has: * Last_Name * First_Name
I was thinking many do a DUAL comparison...Let's say compare the last four digits numbers and the last and first name from table 2 vs the last four digits and the last and first name from table 1.
Hi, Wish if some one could help me ASAP. I have a table which contains name, tel, email i need to import only records which have distinct email. I do need need to import data of all three fields but only which has distinct email. As there are number of record which are duplicate. They have different names but same email. So i need to condition only for distinct email but dump the data in a new table with all three records. so same names can have different email. but same email can't have duplicate email. So need only records which have distinct email. Please help .......
I have this table ("people") and an example of possible rows:
id(key) COL 1 COL2 department country name 1 xx yy KPP USA John 2 zz kk KPP USA John 3 ss ff TLL USA John 4 ww qq PPO Italy Marco 5 jj uu PPO Italy Marco
I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).
I have tryed in this way:
SELECT COUNT(DISTINCT department) AS NumberOfDifferentDepartments FROM people GROUP BY name;
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
I'd like to create a query which will consist of simple SELECT statements as follows:
SELECT [table1].[field1], [table2].[field1], [table2].[field2] FROM table1 INNER JOIN table2 ON ([table1].[fieldX] = [table2].[fieldX]);
The challenge arises b/c instead of joining on equal values, such as the following: [table1] INNER JOIN [table2] ON [table1].[field1] = [table2].[field1]
I would like to join based on equivalencies, such as: [table1] INNER JOIN [table2] ON [table1].[field1] = 34 is equivalent to [table2].[field1] = 2;
I do not know the proper syntax, so this is where I need help. I tried to search online without any success.
I've put together a database for my company to track our archived files. When the files are archived, there are three important values pertaining to their location: a list number, a box number, and a barcode number. Each box has a barcode on it, so what I'd like to do is make it so that when any record has both a list and box number entered (since it is dependent on the list number to know which, for example, "Box 1" I'm entering information for), it would automatically insert the appropriate barcode number in the field. It would obviously need a seed for each barcode number to be entered before it would know which one to use, but that could either be based on the first unique entry, or entered another way. If anyone can help me do this, I'd really appreciate it. Also, if my description was insufficient or confusing, just let me know and I'll try to clear it up. Thanks!
Example: If I entered: List # Box # Barcode # 77 4 It might not know what the barcode is the first time. But once I had entered List # Box # Barcode # 77 4 366582 It would know what I was entering. Keeping in mind that I'm not showing the rest of the record, but just this end part, my next entry would look like: List # Box # Barcode # 77 4 366582 77 4 366582 ...the red representing that it would automatically fill that in, because it knows that if I'm talking about Box 4 in List 77, that must be the barcode.
I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.
companyname zip billybobs 68135 dilberts 68137 billybobs 68135
what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?
Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!
Hi all, Please bear with me if I'm asking a dumb question. I've been tasked to create a database from scratch and I've not had much experience working with Access.
**My Question** Using a form is it possible to have the form automatically enter a "value" in one field based on the information inputed by someone in a different field. ie. When some one puts in a value between 0 and 20 in one field it automatically enters a 1 in the other, and when some one puts in a value between 21 and 30 you get a 2 and so on down the line.
I have a table with a few Listbox options. I want one of the columns' values to be determined by the value chosen in another field. How do I do this?
Example: In column A I choose value ABC from the dropdown. I want column B to now offer values in that list of DEF, GHI, JKL. If I chose XYZ in column A instead, I'd want the column B list to show MNO, PQR, STU. Etc.
If I want to sum the percentages from April, May and June only if a column is Not Null, how would I do that?
example
Tbl 1 PK, Month Percent
Tbl 2 FK, Month Enrolled Qty of Rx in the 1st month enrolled Qty of Rx in 2nd qtr Base (if Qty of Rx in 2nd Qtr is null then Qty of Rx in 1st month enrolled)
If Qty of Rx in 2nd QTR is NOT NULL then QTY of Rx in 2nd Qtr * Sum of April Percent+May Percent+June Percent, otherwise Qty of Rx in 1st month enrolled * Month Percent
Im getting stuck on how to sum the percents of April, May and June and then multiplying the result times the Qty ONLY IF the field is not null.
I only know how to create Query's using the design mode. I dont know how to write SQL statements.
Hi, not sure if this is possible or the most economical approach but here goes:
I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.
I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.
I'm stuck! I want to create a form which has a combo box where you select someone's name, and their address appears in another text box on the same form. The names and addresses are in a table called "cardholders".
I'm doing separate combos for first name, surname cos I think that's the only way you can do it.
The fields in the table are credit_add, credit_name & credit_surname. The combos are called "cardholder name" "cardholder surname" etc. This is the code in the text box where I want the address to appear:-
In my database I have 4 tables for a small painting and decorating business.
These are: tblCustomers, tblOrders, tblStock, tblPayments.
I have a form based on tblOrders to add new orders when they come in from customers.
In this form there are 3 important fields: "CustomerID" (Lookup to tblCustomers), "StockID" (Lookup to tblStock) and Quantity (Entered by user).
However, I want a calculated field that will be worked out automatically when the user is entering these details. The calculated field should go to tblStock and grab the "Cost" field.
However the main problem is specifying what price it will grab from the table. I want it to get the "Cost" where "StockID" = (the stock ID selected in the lookup). I then want to multiply the price by the quantity which the user has entered.
The control source should be worded something like: = ([tblStock]![Cost] where [StockID] = StockID)*Quantity
but that isn't valid.
Could anyone point me to a piece of VBA or some way to get around my problem? Many thanks for any help, Stephen
I'm attempting to build a simple database for a martial arts tournament registration. The weight division (Fin, Fly, Heavy, etc.) depends on the age, the sex and the weight. I've tried this several different ways, most recently by using one table called "competitors" with all the entrant's information, and several other tables based on the age class/sex (i.e. I have a table called Age 8-9 Male with the weights and divisions in it, and other tables for the other ages/sex). So, basically, what I'd like to have happen is a report that will lookup in the correct table the division, based on the weight entered. In other words, for a record where the age is 8-9 and the sex is Male, the report compares the weight to the values in the 8-9 Male table, or if the record is a 12-13 Female, it looks in the 12-13 Female table for the weight, and places the correct division in the Division field of the report. Probably going about this wrong.....but any help would be appreciated.
For my study on academic research I need to match patents that refer to academic research as prior work with the actual prior work.
I have two tables (see attached images below).
One regarding AcademicPublications (AP), which is neatly organized with title, year, journal, volume, pages, first author, etc... 480,000 rows
One regarding Patentswhere all this information is hidden within one field, in the most messy way possible... for instance, a field could have:
Quote:
Sugita et al, "Nonsurgical Implantation of a Vascular Ring Prosthesis Using Thermal Shape Memory Ti/Ni Alloy (Nitionl Wire)," Trans. Amer. Soc. Artif. Intern. Organs, vol. 23, pp. 30-34.
or
Quote:
Willingham et al., Cell 13, 501-507 (1978).
Or many other ways.
I want to create a new table that is set up like this:
The question is: How do I match different fields from one table on one field of another and make it return another field (the ID)? Some references are too horrible to match, but I need as many as I can get.
I can imagine two queries would give me the bulk:
A match in [Title] AND [Year]
A match on ([SourceTitle] OR [AbbreviatedSourceTitle] ) AND [Volume] AND [Year] AND [PageStart]
I understand that I have to make use of the Like "*"&[value]&"*", but how do I make it return the matching ID?
