Help needed with Query

[willfrank]

Can someone be so kind to look at attached database: I am trying to create a query that shows the active records only.



Room Name and Phone Number need to correspond with the records that are in the form. I cannot get the query to show it by records.

It displays Room Name and Phone Number only in sequence of table, not actual records. thanks in advance.

[warmslime]

Can someone be so kind to look at attached database: I am trying to create a query that shows the active records only.

Room Name and Phone Number need to correspond with the records that are in the form. I cannot get the query to show it by records.

It displays Room Name and Phone Number only in sequence of table, not actual records. thanks in advance.

I think you'll have to clarify a bit. What is wrong with the query? What were you expecting it to return?
I notice that you've established a relationship between RoomID (of table Rooms) and JobID (of table Incidents). Which is a 1-1 relationship since these fields are keys in their respective tables. However this makes no sense because the Incidents table also contains the RoomID , which in theory should always be equal to JobID (in your table they don't appear to be related).
For example, go to the Rooms table and check Room 1. According to the tables' relationship, the related record in the Incidents table is Job 1, which has a RoomID of 3! See the contradiction?

I also notice mistakes in your form but you really need to work on the bigger picture first. Take a step back and design your database on paper before you even start Access, because right now your configuration makes no sense from a logical standpoint.

[davegri]

Attachment 25123
See the attached. I set up incidents as the recordsource for the main form and added a subform for the rooms.
I had to change the RoomID in the incidents table to Long, and modified the relationships to link the incidents table RoomID to the rooms table RoomID.
I changed some of the datanames in the incidents table to avoid naming conflicts with Access reserved words and special symbols.
Most of the Dlookups are not necessary by having a subform as you can see.
It needs more work, but this should get you down the road a bit.

[ssanfu]

I also think the table structure needs a lot more work.
Granted, I don't know your business, haven't seen any of your data and have not read any requirements, but these are some of my thoughts looking at your dB. No offense meant....


The "Equipment" table should have an Autonumber PK field. (All of my tables have an autonumber PK field)

The fields in the RED square (below) are actually data and shouldn't be field names. What happens if you have to add another type of equipment?? You will have to redesign the entire dB.


The "Rooms" table should only deal with rooms.

Consider.....
So there would be 3 tables:
"Rooms" table
"RoomsID_PK (autonumber)
"BuildingID_FK" (Long)
"RoomName" (Text)
"PhoneNumber" (Text)


"Equipment" table
"EquipID_PK" (autonumber)
"Equip" (Text) (example: Projector)
"EquipType" (Text) (example: Table Nec)
"EquipPhotos" (Text)

"RoomEquip" table (junction table)
"RoomEquip_PK" (Autonumber)
"RoomsID_PK (autonumber)
"EquiptmentID_FK" (Long)
"Technician" (Text)


"RoomEquip_PK" would be the link to "Incidents.RoomEquip_FK"


In "Incidents" table, the fields "BuildingID" and "RoomName" would not be needed/should be deleted.


- Object names should NOT have spaces.
- Should not have "Lookup FIELDS" in tables. See http://access.mvps.org/access/lookupfields.htm


- PK fields should never be displayed. See
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers
(What they are NOT - #6)


Good luck with your project.......

[willfrank]

thanks very much for you guidance and follow up, but unfortunately, the sub form will not work. I am trying to have a user friendly database for all users to access and fill in. I set up the database so Buildings (4) of them can be easily selected and then represented by the appropriate available conference rooms. I do not want the end user to manually input the building number and then conf. room number. Too many conference rooms to keep track up. I just need the query to provide "Active" records based on the records, not the table. Currently the query returns the "Active" results but they are not from the actual records.

[willfrank]

thanks for the feedback. I am novice to access, but I do appreciate what you have provided. I am not familiar with "PK" The additional fields in "Rooms" represent the equipment that is available in the room. Its just a reference. The database was created that many users will input simplistic incidents that occur. I will be the person who looks at the records and determines what projects are "Active" and have to be followed up with. My current query results do not reflect the actual records that were created thru the form. They are referencing the rooms and phone numbers based on the table and not the records. This is my challenge. thanks

[willfrank]

thanks for your help. The query provides me the "Active" results but the results are not equal to the actual records. As example: Room and phone number on the return query does not represent what is in the actual record. See below results from query vs. what I want to see:

Job #	RoomName	PhoneNumber	Status
44	US-PLB-3-2009	609-897-4445	Active
47	US-PLB-3-2201	609-897-4590	Active VS. Job # RoomName PhoneNumber Status 44 US-PLB-3-2009 609-897-4445 Active 47 US-PLB-3-2201 609-897-4590 Active

[willfrank]

willfrank thanks for your help. The query provides me the "Active" results but the results are not equal to the actual records. As example: Room and phone number on the return query does not represent what is in the actual record. See below results from query vs. what I want to see:

Job #	RoomName	PhoneNumber	Status
44	US-PLB-3-2009	609-897-4445	Active
47	US-PLB-3-2201	609-897-4590	Active VS. Job # RoomName PhoneNumber Status 44 US-PLB-3-2009 609-897-4445 Active 47 US-PLB-3-2201 609-897-4590 Active

[orange]

willfrank,

As mentioned in a previous exchange on this subject, I don't think your data model/relationships reflect your business needs.
Can you show us a jpg of your latest/current relationships?

[willfrank]

[orange]

From a concepts view, at least, you should separate the AVEquipment into a separate table. Then a junction table, such as,
RoomHasEquipment to identify which AVEquipment is in which room. Also, a Technician/Staff table with appropriate relationships to Rooms or Buildings. Incidents table would not contain Room Name nor Phone number. A separate Status tale might add some consistency and ease of analysis/reporting. This sort of business "things" breakdown will lead you to use database as intended.

You should Normalize the tables and create the relationships that represent and are based on your Business facts. If you choose to de-normalize later for some purpose important to you --so be it.
Good luck with your project.

[davegri]

The reason you are getting the wrong room number for the Job # is because in your relationships you have a relationship between Incidents Job # and Rooms roomID. So Job 44 pulls information from Rooms RoomID 44. WRONG!
If you look at the incidents table you will see that Incidents Job 44 should pull info for Rooms RoomID 56. RIGHT!
You need to set your relationships so that Rooms.RoomID is linked to Incidents.RoomID. Then the query will know that Incident Job # 44 refers to rooms RoomID 56 which is US-PLB-3-5103. Does that look better?

[willfrank]

Thank you for your suggestions: I will try to do so; but my priority right now is to create a query that will give me "my open ACTIVE" jobs based on the forms records. If you can not provide me, I understand.. thanks again.

From a concepts view, at least, you should separate the AVEquipment into a separate table. Then a junction table, such as,
RoomHasEquipment to identify which AVEquipment is in which room. Also, a Technician/Staff table with appropriate relationships to Rooms or Buildings. Incidents table would not contain Room Name nor Phone number. A separate Status tale might add some consistency and ease of analysis/reporting. This sort of business "things" breakdown will lead you to use database as intended.

You should Normalize the tables and create the relationships that represent and are based on your Business facts. If you choose to de-normalize later for some purpose important to you --so be it.
Good luck with your project.

[orange]

willfrank,

but my priority right now is to create a query that will give me "my open ACTIVE" jobs based on the forms records.

Your basic issue is the structure of your database.
Have you ever heard this expression
I'm too busy chopping wood to sharpen the axe? I think it applies to your situation. As long as you're so busy querying a poor structure, you won't have time to design the database to process it efficiently with Access.

Work through and/or watch some tutorials on Normalization.
Here's an article that may help.

Good luck.

[ssanfu]

I am trying to create a query that shows the active records only.

This is easy.
The "Incidents Active" query (Post # 10) should work. (shouldn't have the space in the name)
The form "TCSIncidents" should have the form "Incidents Active" set to the query "Incidents Active", not the table ""Incidents Active""


Be warned that you have many problems with your dB.
- You have spaces and special characters in object names.
- Your tables are not normalized


Good luck with your dB...