Recent content by pickslides

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...