Combobox/COUNT

[SKJ]

Hello everyone. I am very new to the MS access and can't figure it out about several issues.(Please see attachment)
Issues-

• My BUILDING DETAILS query is not showing correct count of floor for that building and correct count of organization which are present in that building.
• I don't know how to create a combo box which allows me to select only one building at a time so user can see only that building's information.

• I want to create a combo-box

1.Select Building(Building name should be displayed there)
2.Select Organization(name displayed)
3.Execute query to create report (based on 1 and 2)
I am using MS access-2010.
PS-I have deleted many forms and data from above attachment to reduce file size.
Can someone pls guide me about where I am going wrong.

Thanks!
SJ

 

[bob fitz]

Some of us are still using A2003. You may get more people to look at your db if you convert it to the earlier mdb format.

 

[bob fitz]

I expect that you are unable to convert the db to an earlier version because you have used features that were not available in the earlier version. Just changing the file extention will not work. Unless you can strip out the new features, I will be unable to view the db, but I am still willing to help if I can.

<LI itxtNodeId="14" itxtHarvested="0">My BUILDING DETAILS query is not showing correct count of floor for that building and correct count of organization which are present in that building.

Can you post the SQL statement of this query.

 

[SKJ]

I don't know any other way to save this file then as I don't have any older version
I added in the report design to combine it as- =Count([Orag_Name])
Here is the SQL code.

SELECT ORGANIZATIONS.Orag_Name, [BUILDING FLOOR DETAIL].FL_ID, Sum(ROOMS.Room_AreaInSqFt) AS SumOfRoom_AreaInSqFt, Sum(ROOMS.Room_Occupancy) AS SumOfRoom_Occupancy, Sum(ROOMS.Room_Rent) AS SumOfRoom_Rent
FROM [ROOM TYPES] INNER JOIN (ORGANIZATIONS INNER JOIN (BUILDINGS INNER JOIN ([BUILDING FLOOR DETAIL] INNER JOIN ROOMS ON [BUILDING FLOOR DETAIL].FL_ID = ROOMS.FL_ID) ON BUILDINGS.Bldg_ID = [BUILDING FLOOR DETAIL].Bldg_ID) ON ORGANIZATIONS.Orag_ID = ROOMS.Orag_ID) ON [ROOM TYPES].Room_Type_ID = ROOMS.Room_Type_ID
GROUP BY ORGANIZATIONS.Orag_Name, [BUILDING FLOOR DETAIL].FL_ID;

I appreciate your time in this.

Thanks!
SJ

 

[SKJ]

so insted of counting organization as once it counts all the entries. Correct number should come 10 as I have max 10 records in my organization table. But it is returning 52 as a count.

 

[SKJ]

I am not sure how to add Distinct to Organization ID and to FL Name to get the unique count of the organizations and floors. I have 10 organization and 15 floors in my table
here is my code. I am sure it's not that great but I am learning.

SELECT BUILDINGS.Bldg_Name, BUILDINGS.Bldg_Address, BUILDINGS.Bldg_City, BUILDINGS.Bldg_State, BUILDINGS.Bldg_Zip, BUILDINGS.Bldg_Manager, BUILDINGS.Bldg_ContactNo, Count((ORGANIZATIONS.Orag_ID)) AS CountOfOrag_ID, Count([BUILDING FLOOR DETAIL].FL_Name) AS CountOfFL_Name, Sum(ROOMS.Room_Occupancy) AS SumOfRoom_Occupancy, Sum(ROOMS.Room_AreaInSqFt) AS SumOfRoom_AreaInSqFt, Sum(ROOMS.Room_Rent) AS SumOfRoom_Rent

FROM [ROOM TYPES] INNER JOIN (ORGANIZATIONS INNER JOIN (BUILDINGS INNER JOIN ([BUILDING FLOOR DETAIL] INNER JOIN ROOMS ON [BUILDING FLOOR DETAIL].FL_ID = ROOMS.FL_ID) ON BUILDINGS.Bldg_ID = [BUILDING FLOOR DETAIL].Bldg_ID) ON ORGANIZATIONS.Orag_ID = ROOMS.Orag_ID) ON [ROOM TYPES].Room_Type_ID = ROOMS.Room_Type_ID

GROUP BY BUILDINGS.Bldg_Name, BUILDINGS.Bldg_Address, BUILDINGS.Bldg_City, BUILDINGS.Bldg_State, BUILDINGS.Bldg_Zip, BUILDINGS.Bldg_Manager, BUILDINGS.Bldg_ContactNo, BUILDINGS.Bldg_ID

HAVING (((BUILDINGS.Bldg_Name)=[Bldg_Name]));

Thanks!
SJ