Search results

I ended up solving the problem by piggy backing some queries. Firstly... SELECT [Purchase Table].Company, [Purchase Table].[Purchase Date], [Purchase Table].ID, Count([Purchase Table].[ID]) AS IDcount FROM [Purchase Table] GROUP BY [Purchase Table].Company, [Purchase Table].[Purchase Date]...

Could be my explanation! :) If an ID appears more than once on a certain date it should only be counted as one. Hence in Company D there are 26 records and reduced to 20 records once I count ID when grouped by Purchase Date and ID All I need to do now is to find a way of counting the rows...

I think i'm getting closer. I have: SELECT [Purchase Table].Company, [Purchase Table].[Purchase Date], [Purchase Table].ID, Count([Purchase Table].[ID]) AS IDcount FROM [Purchase Table] WHERE ((([Purchase Table].Company)="D")) GROUP BY [Purchase Table].Company, [Purchase Table].[Purchase...

Hi jdraw, thanks for your post. This is kinda what I am looking for, but I am after the distinct count of IDs within a date. So this code: SELECT [Purchase Table].Company, Count([Purchase Table].ID) AS CountOfID, Sum([Purchase Table].[Pruchase Amount]) AS [SumOfPruchase Amount] FROM [Purchase...

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.

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

Hi thanks for the response. I have seen this done before and the developer linked the machine's username from the profile or something similar. It captured the username as part of the record.

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

By double clicking the query name from the list or using the Datasheet View button. :)

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

Use a label instead is the answer..

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

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?

Thanks for the clarification people. A/(B-C-D) represents a service level in a call centre. A to D are different call centre metrics.

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

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.

That worked really well. Now how do I sort on the Total column that MS Access has in the crosstab wizard by default.

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

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

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