Search results

So always pigging backing queries? If I had my subjects named 7Math , 8Math, 7Sport, 8Sport and so on, then I could get distinct count with one select query?

Thanks, this is great. Can this be done in one select query? Let's say the crosstab isn't needed. Q

Hi all, Please see DB attached. I wish to count how many students are in each subject by year level Output for this table would be a crosstab (select query is fine) Subject / Year Level .... 7 ..... 8 Math ...... 8 ....... 5 Sport...

It does count what I want but the row fields are repeated and the values are cascaded by date. Think the Group By has row headers is the problem

Hi there, I have this query, does not return what I am after, yet it does with the same logic in Excel with a Countifs Just looking to count the number of times the number 49 appears in field Absence Minutes as my cross tab value TRANSFORM Sum(IIf([Absence Minutes]=49,1,0)) AS CountOfMins...

I am doing some counts and sums but when I use a WHERE for a criteria, it applies this to all the sums and counts. How do I construct a query where the criteria only applies to a particular sum or count while keeping the others unaffected? SELECT Students_Concern.UID...

It is true that TIMESHEET returns all 281 records but I want to append the following fields to this table Append catchment.display_text Using the relationship Catchment.entity_id = CASES.assignee_user_id AND Append Subtype.display_text Using the relationship Subtype.entity_id = CASES.id AND...

Yeh I have tried to link everything back to the TIMESHEET table with different types of joins and losing records

Thx Paul, attached the DB in post #1 :)

Hi there, I have attached an XLS file I wish to replicate in Access using querie(s). I seem to be losing to many records with my attempts. The XLS sheet has 281 records when appending data using lookups and the Access Query only has 213 records. MQ My DB file was 2.3 MB so it couldn't be...

Here is the SQL SELECT Cases.reference_number, Fddvs.field_name, Fddvs.display_text FROM Fddvs RIGHT JOIN Cases ON Fddvs.entity_id = Cases.id GROUP BY Cases.reference_number, Fddvs.field_name, Fddvs.display_text HAVING (((Fddvs.field_name)="case_sub_type")); I can do the same task in excel...

I have 2 tables. Table A has 52832 records. I made a join in design mode such that Table A ----------> Table B When I look at the datasheet view there are only 52831 records, why would one drop off? I thought this type of join preserved all records in Table A? MQ

I have combined the 2 and solved the problem. Thx for all interested UTC: IIf([start_time] Between 417350.125 And 419170.083333333 Or Between 420990.125 And 422810.083333333 OR Between 424630.125 And 426450.083333333 OR Between 428270.125 And 430100.083333333 OR Between 431910.125 And...

I have SELECT IIf([start_time] Between "6/4/2014 3:00 AM" And "5/10/14 1:59 AM" Or "5/4/2015 3:00 AM" And "4/10/15 1:59 AM",[start_time]+(10/24),[start_time]+(11/24)) AS UTC FROM Timesheet_entries; which gives #Error in the field. I think the format is an issue, maybe the AM part? I also...

I have a date field in minutes, seconds always '00' i.e. 24/04/2015 1:23:00 AM as short text and want to adjust this date to UTC time as follows. before 6/4/2014 2:59 add 10 hours. between 6/4/2014 3:00 AM and 5/10/2014 1:59 AM add 11 hours. between 5/10/2014 2:00 AM and 5/04/2015 2:59 AM...

Here is the code TRANSFORM Sum([amount_cents]/100) AS RAP SELECT Fddvs.display_text FROM ((Cases LEFT JOIN Fddvs AS Fddvs_1 ON Cases.assignee_group_id = Fddvs_1.entity_id) INNER JOIN Payments ON Cases.id = Payments.case_id) LEFT JOIN Fddvs ON Cases.assignee_group_id = Fddvs.entity_id WHERE...

I have a table with a date field as short text datatype. Data looks like 2015-05-22 04:45:10.977 MY query wont work when criteria is Between "2015-07-01" And "2015-10-01" Any tips?

Thx for the reply. Not sure I follow, i'm making a variable for "make" i.e: SELECT case.cust_id, case.Name, case.Start_Date, details.Display_text, details.Display_text AS Make, Sum([purchase details].purchase_amount) AS SumOfpurchase_amount, Sum([purchase details].Sales_tax) AS SumOfSales_tax...

I have attached a DB that has three tables. In query 1 these are joined. I wished to add a column in my query for "Make". This is "Display_text" where field_name="Make". The problem is I have already used this combination to bring across "Gender" from field_name Kind regards, MQ

I have the following code where I am trying to limit the query to pick up the last 12 or 13 months of data only. TRANSFORM Round(Avg([Net Price]),0) AS [Avg Net Price] SELECT [TIP Insurance Raw Data].[Agency Group Name] FROM [TIP Insurance Raw Data] WHERE ((([TIP Insurance Raw Data].[Agency...