One to many query, summation query.

[Lifeseeker]

Hello,

I have a database that's based on a one-to-many relationship.

Two tables, facility and storage space.

Both facility and the storage space have chairs, and we are interested in the number of chairs in both the facility and the storage space.

A facility can have multiple storage space, but a storage space can belong to only one facility.

The other thing is that the facility can be located in a different city than the storage space. So you can have a facility that is located in city A, and all its storage space is in different cities.

How would I be able to produce a query that simply tell users how many chairs there are in a given geographic location without counting the facility multiple times?

I have attached a small screenshot of what the data looks like:

So in the screenshot, you are see repeating Fs. This is because they all have a S attached to it.

Take F0103 as an example in the screenshot. Although there is 0 chair, I do not want to count 0 five times, for F0103 is repeating 5 times. If F0103 is expanding to include more chairs, then I don't want the query to count those chairs just because the facility is repeating. The facility is the same, but it's repeating because of storage space attached.

The total sum query is not an issue for storage facilities as you can see in the screenshot.

Is there a way around this? So to keep the facility list unique while still having a breakdown of the storage?

Any thoughts/comments much appreciated.

 

[jdraw]

Can you show us the database tables and relationships?

When does someone take counts? How do you update the database?
Is this a real world problem or an exercise of some sort?

 

[Lifeseeker]

Please see the attached database file. It's an example of the problem I'm having.

As you can see, the relationship is one to many. Under query, you will see the total chair counts.

We get 20 under lab because it's 5 + 5 + 7+ 3. The problem is that I would like to count 5 only once since it's just the same lab and we don't have to count chairs twice for the same lab. So the correct chair count for lab facilities should be 5 + 7 + 3, and the total chair count should be 5 + 7 + 3 + 11, which is also what the user would like to see.

11 is correct as it's just a summation of all the B facilities.

Any thoughts/comments?

Thank you

 

[Lifeseeker]

Please see the attached database file. It's an example of the problem I'm having.

As you can see, the relationship is one to many. Under query, you will see the total chair counts.

We get 20 under lab because it's 5 + 5 + 7+ 3. The problem is that I would like to count 5 only once since it's just the same lab and we don't have to count chairs twice for the same lab. So the correct chair count for lab facilities should be 5 + 7 + 3, and the total chair count should be 5 + 7 + 3 + 11, which is also what the user would like to see.

11 is correct as it's just a summation of all the B facilities.

Any thoughts/comments?

Thank you

Could someone help me on this please?

 

[Brianwarnock]

I thought that you wanted to sum by location, that does not seem to be possible unless we are to assume that the location is the first letter of the Labid !!

Personally i don't think this is possible in 1 query.
To sum geographically you are not interested in the relationship between the Lab and the facility although you may want to know which lab the facility belongs to even though it is in a separate state.

Brian

 

[Brianwarnock]

Did this get resolved?

 

[Lifeseeker]

Hi, I appreciate your help.

I'm not sure either if this can be done, but it sounds just like a query problem?

Right now I can actually generate a report based on lab and on the report it counts all the labs once and I did a "sum" and "group by" for all the chairs in the corresponding storage facilities. This works out well except users can't see which lab or storage in which geographic location.

In the attached database, if you go to query(chair distribution), you will see what I mean. So I don't want to count the 5s twice because it's just the same lab and users will just want to know where that lab is, and it's in Toronto.

So really the total for that lab should just be 5 and not 10, and at the bottom that number 20 should just be 15.

I keep thinking that this is a simple request, but I'm just not sure how to do it without changing the database structure.

 

[Brianwarnock]

This from post 1 made me think that you wanted any report to be by location, as it turns out that is address, but what the heck, so that would be the grouping, am I wrong?

How would I be able to produce a query that simply tell users how many chairs there are in a given geographic location without counting the facility multiple times?

so for Toronto

adrress lab_chair_quantity csc_chair_quantity lab_id
toronto 5 0 B0001

ok maybe more info but not more entries.

Brian

 

[Lifeseeker]

This from post 1 made me think that you wanted any report to be by location, as it turns out that is address, but what the heck, so that would be the grouping, am I wrong?



so for Toronto

adrress lab_chair_quantity csc_chair_quantity lab_id
toronto 5 0 B0001

ok maybe more info but not more entries.

Brian

Hi Brian,

You are right. I was wrong at the beginning and I see it now. If users want to do filter based on geography for both lab and storage facility, then they will miss out on records because not all storage facilities and labs are in the same city.

What I mean is....

In the attached database, so if a toronto lab is borrowing a storage facility in hamilton, and you have another lab that is in Hamilton with the storage facility located in Hamilton. When users want to see all the labs and storage facilities that are in Hamilton, then the one lab that is in Toronto will get filtered out because its storage facility is in Hamilton......and the chair in that storage facility won't be counted because its lab has been filtered out.


So it's prolly not right to do reports in one place. Users would have to do TWO reports. one for lab and the other one for storage facilities.


Sorry about the confusion. I was confused by myself there in the first place.

 

[Brianwarnock]

Ok so is the Report rptchairs in the attached the type that you would want for a geographical breakdown, obviously it could be filtered.

Brian

 

[Lifeseeker]

Ok so is the Report rptchairs in the attached the type that you would want for a geographical breakdown, obviously it could be filtered.

Brian

Oh I see.....a union query will do the trick. Plus it's not counting the chairs for the same facility multiple times. Thank you very much on this Brian.

One question though.....why is it that I cannot update directly on the union query in datasheet view? It's saying that the recordset is not updatable. Is it because the union query doesn't allow updates to be made to the record?

 

[Brianwarnock]

To be honest I don't have a lot of experience with UNION queries but I would expect them to be nonupdateable.

Brian

 

[Lifeseeker]

To be honest I don't have a lot of experience with UNION queries but I would expect them to be nonupdateable.

Brian

I see. It's okay if it is not updatable. I will just tell users to update them individually.

I have just ran into a problem regarding the union join. The problem is that the City field is actually a look up field from another table. The reason I did not include it in the all these TEST versions is to keep it simple.


In individual queries, all the city names are displayed properly in datasheet view. When you combine the two queries using a union query, the city field turns into number, as you can see from this copy.

I have tried to change the column bound, but having no luck.

any help or comments much appreciated.