Queries :: Concatenate Multiple Records Grouped By Date?
May 2, 2014
I have a rates table with 3 fields : [RateDate], [CurrencyID], [FXRate]
I also have a currencies table with 2 fields : [CurrencyID], [CurrencyCode]
Where [CurrencyCode] is just the 3-character currency code (i.e. EUR, GBP, USD etc.)
The rates table uses a composite primary key across [RateDate] and [CurrencyID] - i.e., there should only be one unique record for each combination of date and currency.
Not every date will be present in the table and, within each of those dates, not every currency will be present (but at least one, obviously)
I want to write a query which will return two fields. The first field will contain all of the unique dates in the table for which the [FXRate] field (for any currency) is blank / null. Straightforward enough :
Code:
SELECT [tblRates].[RateDate]
FROM [tblRates]
INNER JOIN [tblCurrencies]
[Code]....
But this gives me a separate record for each date / currency combination in the returned dataset. I just want one record per date and all of the currency codes in a single string (separated by some delimiter) as a text field.
I have a database that tracks students and their test scores. I am trying to figure out a way to where it will pull the student's latest test score and compare it to see if they fall within standards. So far I have 3 tables.
The query I have written looks like this: SELECT tblStudents.studentID, Max(tblResults.resultDate) FROM tblStudents LEFT JOIN tblResults ON tblStudents.studentID = tblResults.resultStudent GROUP BY tblStudents.studentID." The data pulls just fine at this point. What I'm trying to figure out is how to then add more columns to this query to start doing comparisons. When I try to add more columns it tries to use them for additional grouping and adds many more records.
tblSFDC (contact information) includes CustID (primary key) and a memo field "Notes"
tblTheCall (call information) includes CustID (relates to tblSFDC) and "activity data fields" ie "Live", Left Message" , Sent email".
tblTheCall may in some cases have multiple records for each CustID
I need to : Concatenate from tblTheCall Field Names and field values (that are not null) Insert into tblSFDC.Notes Where tblTheCall.CustID = tblSFDC.CustID
I am trying to concatenate multiple rows into one record. I googled it and found many functions in VBA that do the job. However my problem is that my query will be linked directly to Excel file and then I get an error message saying that Excel could not recognise this function. I could potentially insert data into new table and link that table to Excel but I need to avoid end-user exposure to Access as much as possible.
So I am desperately trying to find a way of doing this without VBA code.
Example of data:
Customer Name |Order Number Smith |O101 Brown |O102 Smith |O103 Green |O104 Brown |O105
I am trying to achieve below:
Customer Name |Order Number Smith |O101, O103 Brown |O102, O105 Green |O104
In my query, I have several results that relate to the same PK overall, and I'd like to concatenate these records into one expression/one field in a form.
Currently my query looks like this;
As you can see, the BandPK/BandFK are repeated where the GenreFK/GenrePK are different. What I'd like to do is concatenate the column named Genres into one field so the BandFK/PK isn't repeated.
I have a query that selects the "Active" records for three different selections, A, B or C.. There may be 1, 2 or 3 results for a particular selection. That is Selection A may have one result or active records, but Selection B may have three results.
I want to use data from the query to populate a field on a form. For example, if the results for Selection A, having one "Active" record would be RecordAData. But for Selection B with three "Active" records the result would be RecordBData & " " & RecordBData2 & " " & RecordBData3
My query is: SELECT tblSomething.ID, tblSomething.D1, tblSomething.D2, tblSomething.D3, tblSomething.D4, tblSomething.D5, tblSomething.D6, [D3] & " " & [D4] & " " & [D5] & "-" & [D6] & " " & "SomeText" AS Header FROM tblSomething WHERE (((tblSomething.D1)=Forms!frmSomethingHeaders!D1) And ((tblSomething.D2)=True));
The concatenation in the query is labeled "Header". I want to be able to Concatenate the "Header" which in itself is a concatenation in the query.
I thought that this might be a looping through the query results, but I cannot figure out how to do it. But then, that is only my uneducated guess.
I use MS Graph and need show 2 data types: 1. Invoice Value, EUR 2. Date Date axis group records by month, for Example Period from 2005.01.01 And 2006.01.01 has 12 positions in Date Axis. Her I have problem: I need show sum of Values for every month. For example in Period from 2005.01.01 till 2005.02.01 I have 3 Values: 200, 500, 600EUR. Her I need to show 1300EUR, and so every month. Her is my strandart code:
Dim strSQL1 As String strSQL1 = "SELECT Date As Date, Value FROM Table WHERE (Date BETWEEN 2005.01.01 And 2006.01.01)" Me!chrStatistik.RowSource = strSQL1
This probably is a very stupid question but I am a new user and have been pulling my hair out for a week over this. I have created a report and linked it to my table but it generates a list of all of the values in my table on the report. What I want is a way to generate a report page for every row in my database table grouped on a key record that is unique to each data row. If anyone has any advice it will be much appreciated, thanks.
I have a totals query of phone call charges by phone number and I want to show a "budget" amount for each number (same amount for all) and a variance against that budget.
How I can do this at the grouped (phone number) level rather than at the individual call level?
Please see current query below:
SELECT Call1CurrentTbl.電話番号（MSN）, Sum(Call1CurrentTbl.料金) AS 料金OfSum FROM Call1CurrentTbl GROUP BY Call1CurrentTbl.電話番号（MSN）, Call1CurrentTbl.[レコード区分], Call1CurrentTbl.表示区分 HAVING (((Call1CurrentTbl.[レコード区分])=2) AND ((Call1CurrentTbl.表示区分)=10));
I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.
So the data looks like this:
Maths 54 Maths 69 Maths 41 English 71 English 55 Art 44 Art 43 Art 66
I would like to write a query that selects only the highest score for each subject and presents it like this:
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I have simple table data structured as follows;[Origin], [Code], [Weight]. The Code field is a lookup field that will contain one of 8 choices; 10, 13, 13c, 23, 25, 27, 27a, & other. The other fields are pretty much self explanatory. Sample data would look like this:
What I need to create is a totals query where I first group by origin value, then a column for each "code" value which totals the weight for that "code". A sample output would look something like this:
The only way I can think to accomplish this is to restructure the original table to include a field for each code and enter the weight in the appropriate 'code' field. If this is the only solution then Ill have to live with it, but is there any way to create this output using my original structure?
I got this far: SELECT DISTINCT count(a.ID) as mCount, year(dimDate.Date) as [Year], month(dimDate.Date) as [Month] FROM admin AS a INNER JOIN dimDate ON (dimDate.Date >= a.sDate AND dimDate.Date <= a.eDate) OR (dimDate.Date >= a.sDate AND dimDate.Date = null) GROUP BY year(dimDate.Date), month(dimDate.Date) ORDER BY year(dimDate.Date), month(dimDate.Date)
these are then used as the start of a between lookup against [tblMaster] along with the last record in the qryPeriod (2014 - 04)...I will need 4 queries so If someone can sort out last period - 8 I can probably do the others.would DLookUp do this say return the last 4 values, Last 6 Values and Last 8 Values
I'm very new to Access and I'm attempting to write an expression in a query that will calculate the % of the count of "Exchange" field (Exchange is a text field and is grouped and the count based on each unique name) where the total count is based on the filter where "Group" = 'FS' or 'S'.
The below seems to work, but there is a better way of going about this (especially if I have to add more filter criteria). I added a pic of the query I'm trying to build.
PercentofTotal: (Count([Exchange])/DCount(Count([Exchange]),"[Holdings]","[Asset Group] = 'FS' OR [Asset Group]='S'"))
I have a table of transactions (close to 1m records) from which I want to query totals by currency and direction (ins and outs) and then also show the proportion of the overall total for each currency. All amounts are absolute (i.e. ins and outs both represented by positive numbers)
To throw some added complexity into the mix, the transaction table records only refer to account (AccountID), which links to a separate account and currency tables.
I can query the sum totals by currency and direction (although I'm not totally happy with the SQL syntax .
I'm struggling to get the proportions per currency though? I tried adding :
Code: ((SumPayUSD+SumRecUSD)/Sum(tblTransactions.USDAmount)) AS Proportion
But it tells me that the specified field 'tblTransactions.USDAmount' could refer to more than one table listed in the FROM clause of the SQL statement.
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping.
I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.
Value 1: FormRegNo CombinedParticipants DC-190 Political parties, CSOs, community residents
Value 2: FormRegNo CombinedParticipants DC-190 Political parties, CSOs and community residents
Function: Option Compare Database Option Explicit Public Function ConcatField(strSQL As String) As String Dim dbs As Database Dim rst As DAO.Recordset Dim strConcat As String
I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
I have a table called Export_Car which contains Data such as License Number and Make and Model.I am calling a VBA function (in my query) that I have found online.This works fine and concatanates all the License Numbers for that Dealer.
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
