Search results

  1. P

    Count distinct entires

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

    Countifs CrossTab not working

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

    Calculations using WHERE

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

    Query to match XLS lookups

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

    Joins in Query Design

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

    Adjusting date for UTC

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

    Time date query

    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?
  8. P

    Query using stacked data

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

    Date Range Criteria

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

    Complex Query

    Hi there, I wish to write a query that counts the number of unique IDs by date and then sums then by company. Sheet attached. For example company D has 26 records but the amount of unique Ids when grouped by date is only 20. Kind regards, MQ.
  11. P

    Expressions with different Where clauses

    I am used to writing simple queries like SELECT [Marketing].[Month], Sum(iif([Category]="Y",1,0))/Sum(iif([Category] is not null,1,0)) AS BookingSuccess FROM [Marketing] WHERE [Batch]="1" How can I have different WHERE clauses for different expressions? I.e. SELECT...
  12. P

    Staff trackerq

    I want to know if there was a piece of code, query or template that exists that tracks staff movements. Ultimately this would be a button a staff member would push and the DB would create a record of when that button was pushed. This could be used to track when staff login or logout for...
  13. P

    Query Parameters

    In the following code I have a parameter 'RegionPrefix' that filters my data by the first 2 characters of the 'Queue' field. How do I populate the 'RegionPrefix' parameter so it returns all of my data? Currently if I leave it blank it returns nothing. PARAMETERS RegionPrefix Text ( 255...
  14. P

    Text Box

    Why does #Name? appear when I go to form view?
  15. P

    Totals in a Select Query

    After you "group by", is there a way to have the next field presented without a sum or avg etc.. if I know there is only ever one value to present?
  16. P

    Calculation within a Crosstab

    I have the following database, in my query I am trying to do a calculation as the value parameter within the corsstab. Ultimately I an trying to get month as the Row Header (from date) and Category as the column header. The Value inside the table I am trying to calculate is A/(B-C-D) Many...
  17. P

    % in a Crosstab

    Hi all, DB attached. I want a crosstab with TYPE on the column, MONTH in the rows and the summed numerator / summed denominator as a % calculated. Hope this makes sense.
  18. P

    Sorting Month/Year within a Crosstab

    Hi all, i'm sure there will be a quick fix for this problem. I have a crosstab where I want year and month in the header so I have formatted a variable MTHYR to be of the form mm/yyyy. This works well as Access only wants one variable in the column header. My problem is that it is not...
  19. P

    Determine date given Day of year and year.

    I have fields [DayOfYear] and [Year] can I somehow produce the dd/mm/yyyy from this. I know how to do it in Excel but the Asscess function Date() is a little different. I.e. if [DayOfYear] =152, [Year] = 2014 then [Date] = 2/6/2014 MQ
  20. P

    Table Error

    I want to go into Design view of my table but I get this error. 'Either an object bound to table 'Service Request Table' is open or....' No one else is in the DB and I have no other tables, queries, forms, reports etc open.. MQ
Top Bottom