details query

[basilyos]

i don't know if i can describe my problem but i will try

i have a table
Name | Date | Type
Roy | 1-1-2016 | ANV
Tony | 5-1-2016 | ANV
Grey | 12-1-2016 | XEV
Roy | 14-1-2016 | XEV
Tony | 18-1-2016 | XEV
Grey | 22-1-2016 | XAV
Roy | 24-1-2016 | XAV
Tony | 25-1-2016 | ANV
Grey | 26-1-2016 | XEV
Roy | 27-1-2016 | XAV


what i want is to get this result

NAME | ANV | XEV | XAV
Roy | 1 | 1 | 2
Tony | 2 | 1 | 0
Grey | 0 | 2 | 1


so how can i do it?
thank you in advance

 

[Frothingslosh]

You'll want what's called a 'Crosstab Query'.
Basically, create a new query in the query builder, choose 'crosstab' in the 'query type' tab, then enter your Name field as 'row heading', your Type field as 'column heading', and 'Date' as 'value'. For Name and Type, set them to 'Group By' in the total row, while Date gets set to 'Count'.

Also, you need to be aware that Name, Type, and Date are all reserved words and will cause you no end of problems if those are the actual field names. Instead use things like 'SignupDate', 'AppointmentType', and 'ClientName'. Also, avoid spaces and non-alphanumeric characters for much the same reasons.

 

[sneuberg]

I can't see what role date plays in this but if you can filter that out I think you might be able to do this with a crosstab query. In the CREATE tab click Query Wizard, then select Crosstab Query Wizard.

 

[Frothingslosh]

I win!!!!!!!

 

[basilyos]

guys another question plz

how could i filter this data
between two dates

i put on the criteria of the date field
>=[forms]![form1]![date1] And <=[forms]![form1]![date2]


but i get this erroryou cant specify criteria on the same field which you entered value in the crosstab

any help?

 

[Frothingslosh]

Enter a second date field column to the query. In the 'total' row, enter 'Where'. Then in 'criteria', put

Between [forms]![form1]![date1] And [forms]![form1]![date2]

Also, are these the actual object names in your database?

 

[basilyos]

no sir
it's not my object names

and it doesn't work coz am getting an error

the microsoft access database engine does not recognize
'[Forms]!frm_inspections_search]![StartDate]' as a valid field name or expression

 

[sneuberg]

[Forms]!frm_inspections_search]![StartDate]

is missing a bracket. Try:

[Forms]![frm_inspections_search]![StartDate]

 

[basilyos]

sir here i forget to put the bracket but in access all the brackets is writed

so any idea?

 

[Minty]

In a cross tab query you have to specify the parameters. Right click the main query window and select Parameters. Then copy your two form references into the boxes and set them as dates.

 

[basilyos]

ok sir thank you so much you helped me a lot

 

[Frothingslosh]

In a cross tab query you have to specify the parameters. Right click the main query window and select Parameters. Then copy your two form references into the boxes and set them as dates.

Whoops, completely forgot that they had to be explicitly listed as parameters. I even went to post the code for a very similar one! (I didn't post the SQL because for some reason the website decided to strip out all the carriage returns I had put in for ease of reading and replace them with the non-breaking space HTML code and then put it all into one line in the code window.) How annoying. Turns out, the only way I can force new lines is by coding in HTML line breaks! I'm also, apparently, no longer allowed to post attachments.)

PARAMETERS [Forms]![frmAuditDateSelector]![cboDatePicker] DateTime; 
TRANSFORM Max(VW_CallsByDayPerson.PctCorrectAudited) AS MaxOfPctCorrect 
SELECT VW_CallsByDayPerson.CalledBy 
FROM VW_CallsByDayPerson 
WHERE (((VW_CallsByDayPerson.CallDate)=[Forms]![frmAuditDateSelector]![cboDatePicker])) 
GROUP BY VW_CallsByDayPerson.CalledBy 
PIVOT VW_CallsByDayPerson.CallDate;

NOTE: Despite how AWF is formatting it, this was supposed to be multiple paragraphs. I'd post a screenshot of a very similar query, but I literally cannot.