dcount in query

[mohamedmatter]

query name total of devices in school
table1 filed id, schoolname
table2 filed id-lab ,lab name,device-name;device-kind
i want query count device in lab in school by dcount
error
=dcount("[id-lab]";"[table2]";"[device-name]='tv' and [device-kind]='samsung' and [id-lab]="& [id]) when run query error
any hlp

 

[CJ_London]

what is the error that you get?

If this is a query, then you don't need the = before dcount

Also, usual requirement is for a comma, not a semi colon between the dcount parameters, but that can be different depending on the language you are using - however need to ask just to eliminate it as a possible error.

you could just link the two tables on [id-lab]=[id] and use a group by query - something like

SELECT ID, Schoolname, device-name, device-kind, count(id-lab) as totaldevices
FROM table1 inner join table2 on table2.[id-lab]=table1.[id]
GROUP BY ID, Schoolname, device-name, device-kind

 

[mohamedmatter]

error in bmp

 

[CJ_London]

assuming you are referencing my last post, you didn't use what I suggested.

if you are using dcount, you don't use a group by query

 

[mohamedmatter]

what is the error that you get?

If this is a query, then you don't need the = before dcount

Also, usual requirement is for a comma, not a semi colon between the dcount parameters, but that can be different depending on the language you are using - however need to ask just to eliminate it as a possible error.

you could just link the two tables on [id-lab]=[id] and use a group by query - something like

SELECT ID, Schoolname, device-name, device-kind, count(id-lab) as totaldevices
FROM table1 inner join table2 on table2.[id-lab]=table1.[id]
GROUP BY ID, Schoolname, device-name, device-kind

i want this query but devicename and devicekind in heading column
and one row for school A for example

 

[mohamedmatter]

assuming you are referencing my last post, you didn't use what I suggested.

if you are using dcount, you don't use a group by query

i want this query but devicename and devicekind in heading column
and one row for school A for example

 

[CJ_London]

not sure what you are asking - the example query I provided does that

 

[mohamedmatter]

not sure what you are asking - the example query I provided does that

I am sorry don’t explain request for you
I attach my file school database my request in query QryByLab
I want correct the formula builder in query to count devciekind = p4 and devicename =computer in Each school separately in this query look for example in this table in this imag

 

[CJ_London]

for that layout, change your group by query to a cross tab

 

[mohamedmatter]

for that layout, change your group by query to a cross tab

many thanks
but
my result of formula is error in query
i want help me to correct my formula to give me the correct result

 

[CJ_London]

provide a database with some test data, the query you are using and what you want as a result based on the test data - use excel to show the required result and I will take a look -probably not today, I have a fairly full day today

ensure the database has been compacted, zip both files and upload the zip file to the forum

 

[mohamedmatter]

provide a database with some test data, the query you are using and what you want as a result based on the test data - use excel to show the required result and I will take a look -probably not today, I have a fairly full day today

ensure the database has been compacted, zip both files and upload the zip file to the forum

the file excel explain the request

 

[isladogs]

Hi
I've done two crosstabs for you.
Neither are laid out exactly as you've shown but you might find them as good or possibly better. If not just adapt as required.

 

[arnelgp]

see query0 uoto query4.
FinalQuery is the result you want but it is not dynamic.

 

[mohamedmatter]

I’m really grateful

 

[mohamedmatter]

see query0 uoto query4.
FinalQuery is the result you want but it is not dynamic.

Thanks a bunch

 

[mohamedmatter]

see query0 uoto query4.
FinalQuery is the result you want but it is not dynamic.

pls
what do you mean ! it is not dynamic ?

 

[mohamedmatter]

see query0 uoto query4.
FinalQuery is the result you want but it is not dynamic.

what do you mean it is not dynamic?

 

[CJ_London]

arnel means that the structure is fixed - will only work for P4/P5 LabA/LabB and LG/Sumsung, however another school would appear on another line

if you have more then you need to modify the final query to accommodate them.

your requirement and data structure is not really understood (at least by me) - I know LG and Samsung are manufacturers - I presume P4/P5 are a model of computer rather than manufacturer? In which case why not have the LG model etc. It feels like they should be in a separate column or table. And why is device name populated with effectively device types?

why have a column 'P4 in Lab A', but not 'Computer in Lab A' when you have 'TV kind LG' but not 'LG kind TV' - the use of the columns is swapped.

And why have an output like this anyway? there are perhaps better ways of doing it (the concatrelated function comes to mind). But without knowing the purpose there is no point in suggesting it.

 

[isladogs]

Expanding on CJ_London's reply, in my earlier answer I gave you two crosstabs where the column headers were dynamic. In other words they would have automatically updated if you added a Sony TV or similar.

Arnelgp gave you exactly what you asked for but that means if you added new items, the columns wouldn't necessarily show them.