dcount in query (1 Viewer)

mohamedmatter · Sep 23, 2018

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 · Sep 23, 2018

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

Code:

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 · Sep 24, 2018

error in bmp

CJ_London · Sep 24, 2018

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 · Sep 26, 2018

CJ_London said:
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

Code:

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 · Sep 26, 2018

CJ_London said:
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 · Sep 26, 2018

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

mohamedmatter · Sep 26, 2018

CJ_London said:
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 · Sep 26, 2018

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

mohamedmatter · Sep 27, 2018

CJ_London said:
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 · Sep 27, 2018

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 · Sep 28, 2018

CJ_London said:
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 · Sep 28, 2018

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 · Sep 28, 2018

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

mohamedmatter · Sep 28, 2018

I’m really grateful

mohamedmatter · Sep 28, 2018

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

Thanks a bunch

mohamedmatter · Sep 28, 2018

arnelgp said:
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 · Sep 28, 2018

arnelgp said:
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 · Sep 28, 2018

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 · Sep 28, 2018

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.

dcount in query (1 Viewer)

Registered User.

Super Moderator

Registered User.

Attachments

Super Moderator

Registered User.

Registered User.

Super Moderator

Registered User.

Attachments

Super Moderator

Registered User.

Super Moderator

Registered User.

Attachments

MVP / VIP

Attachments

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Super Moderator

MVP / VIP

Similar threads

Users who are viewing this thread