Problem with counts in queries (1 Viewer)

wisso1956 · Jul 22, 2017

Hi Everyone
I am an occasional user of access. My basic knowledge is ok but beyond that I struggle.

My problem is a have a table of property information. I have a number of sites and some of the sites have a number of buildings so for example I am trying to use a query to summarise the data for example:

Number of sites - eg 12
Number of buildings - eg 18
Total sqm eg 10000sqm

So for example one site (SPH) might have three buildings SPH1, SPH2 etc another site (eg SATADM) will only have one building.

My table only includes the site code once and the building codes individually so I expect to be able to get a different number for buildings and sites when I run the query - eg. like above.

For some reason though it won't do this - I get 18 sites and 18 buildings. I have tested the query in a different database table with only limited information to test the query and it works how it should so I can't see what's wrong or what I'm doing different.

Can anyone help?

Thanks
Wisso

Ranman256 · Jul 22, 2017

since you did not provide any table structures,
i assume tBuildings table has a SITE field.
if you query soley on tBuildings you will get 18 records, 18 sites, 18 buildings.
But 12 sites are unique.

can you show a query image?

bob fitz · Jul 22, 2017

My table only includes the site code once and the building codes individually

So what denotes that buildings SPH2 and SPH2 are at site SPH2.
If site SATADM had more than one building what might they be called and what name is given to the one building that it does have.

wisso1956 · Jul 22, 2017

I get the right number of sites and buildings from this table but not from my main database which gives me the same number (the larger one) for both.
Thanks

wisso1956 · Jul 23, 2017

This works but doesn't in my main database
Thanks

wisso1956 · Jul 24, 2017

hi everyone - still struggling with this one. To produce a totals query (i.e. one line) that counts
4 sites
8 buildings
and sums the cost information for all the buildings/sites

as indicated by the attached pdf.

At the moment, the queries that I produce will show 8 sites and 8 buildings. Not sure whether the sites and buildings should be in the same table and what should be the primary key.

Thanks

plog · Jul 24, 2017

I can't really follow what you've posted up until now, so could you post 2 sets of sample data?

A. Starting sample data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you hope to end up with if you feed your query the data in A.

wisso1956 · Jul 24, 2017

plog said:
I can't really follow what you've posted up until now, so could you post 2 sets of sample data?

A. Starting sample data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you hope to end up with if you feed your query the data in A.

Thanks for your help. Please see the enclosed. This is a simplified version but the result I want to get in the query is:

Sites 30
Buildings 50
Size 283

I have tried altering unique values/records but has no effect

Thanks

plog · Jul 24, 2017

Your data isn't set up to generate a simple query that can produce those results. It's possible, but very hacky to get there. Essentially you are going to have to determine each of those 3 rows with queries to get each individually.

So what you should do is treat your request as 3 seperate items to build queries for. I'll do the first for you--total sites:

Code:

SELECT [Site & Bdg Codes].[Site Code], "Sites" AS DataLabel
FROM [Site & Bdg Codes]
GROUP BY [Site & Bdg Codes].[Site Code], "Sites";

Paste that SQL into a new query object and name it 'Totals_sub1'. It gets all the unique sites in your table. Then, to get the total unique sites in your table, you use this query:

Code:

SELECT Totals_sub1.DataLabel, Count(Totals_sub1.[Site Code]) AS DataTotal
FROM Totals_sub1
GROUP BY Totals_sub1.DataLabel;

Paste the above SQL into a new query object and that gives you the first record you wanted--total Sites. You will then build the total Buildings queries in the same manner. For Total Size, you wouldn't need a sub-query but just one summation query.

wisso1956 · Jul 24, 2017

Thanks for your advice - I'll give that a go.
Wisso

Problem with counts in queries (1 Viewer)

wisso1956

New member

Ranman256

Well-known member

bob fitz

AWF VIP

wisso1956

New member

wisso1956

New member

Attachments

wisso1956

New member

Attachments

plog

Banishment Pending

wisso1956

New member

Attachments

plog

Banishment Pending

wisso1956

New member

Similar threads

Users who are viewing this thread