Multi table query

%3CLINGO-SUB%20id%3D%22lingo-sub-1178916%22%20slang%3D%22en-US%22%3EMulti%20table%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1178916%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20created%20a%20logbook%20to%20record%20closed%20and%20pended%20issues%20in%20the%20departments%20and%20created%20different%20tables%20for%20each%20department.%20I%20want%20to%20know%20all%20the%20pending%20issues%20from%20all%20the%20departments.%20How%20to%20create%20a%20query%20for%20the%20same.%20Kindly%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-02-18%20144442.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171619iAEAE570E9662FDDC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Annotation%202020-02-18%20144442.png%22%20alt%3D%22Annotation%202020-02-18%20144442.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1178916%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179631%22%20slang%3D%22en-US%22%3ERe%3A%20Multi%20table%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179631%22%20slang%3D%22en-US%22%3EYou%20perform%20tasks%20like%20this%20with%20queries.%20Parameterized%20queries%2C%20to%20be%20precise.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20this%20case%2C%20since%20we%20can't%20see%20anything%20in%20the%20screen%20shot%20called%20%22departments%22%20my%20best%20guess%20for%20this%20query%20in%20this%20case%20would%20include%20something%20like%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3ESELECT%20ID%2C%20%5BRFI%20NO%5D%2C%20RECEIVED%2C%20TARGET%2C%20RESPONDED%2C%20STATUS%2C%20COMMENTS%2C%20%5BCCBA%20RESPO%5D%3CBR%20%2F%3EFROM%20%5BRFI%2FAD3%2FCIVIL%5D%3CBR%20%2F%3EWHERE%20STATUS%20%3D%20%22PENDING%22%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20a%20related%20note%2C%20your%20table%20and%20field%20names%20reflect%20some%20less%20than%20desirable%20naming%20choices.%20We%20try%20NOT%20to%20incorporate%20spaces%20and%20non-standard%20characters%20like%20the%20%2F%20in%20names.%20They%20can%2C%20and%20often%20do%2C%20create%20complications.%20It's%20better%20to%20avoid%20them.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179651%22%20slang%3D%22en-US%22%3ERe%3A%20Multi%20table%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179651%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20have%20a%20much%20more%20serious%20design%20problem%20in%20that%20each%20department%20is%20in%20a%20separate%20table.%20Unfortunately%2C%20queries%20become%20significantly%20more%20complex%20when%20you%20do%20that.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20COULD%2C%20as%20a%20work%20around%2C%20create%20a%20UNION%20query%20that%20includes%20each%20of%20the%20currently%20existing%20tables.%20That%20would%20have%20to%20be%20modified%20from%20time%20to%20time%20to%20accommodate%20changes%20in%20departments%2C%20though.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20strongly%20urge%20you%20to%20correct%20this%20design%20problem%20now%2C%20rather%20than%20continue%20to%20invest%20extra%20work%20and%20complicated%20queries%20and%20code%20to%20compensate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMerge%20all%20of%20them%20into%20ONE%20table%2C%20similar%20to%20the%20current%20one%20shown%20in%20your%20screen%20shot%2C%20with%20an%20additional%20FIELD%20in%20that%20table%20to%20indicate%20which%20department%20that%20record%20applies%20to.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1181175%22%20slang%3D%22en-US%22%3ERe%3A%20Multi%20table%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1181175%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46682%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20solution%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

I have created a logbook to record closed and pended issues in the departments and created different tables for each department. I want to know all the pending issues from all the departments. How to create a query for the same. Kindly help.

 

Annotation 2020-02-18 144442.png

3 Replies
Highlighted
You perform tasks like this with queries. Parameterized queries, to be precise.

In this case, since we can't see anything in the screen shot called "departments" my best guess for this query in this case would include something like this:

SELECT ID, [RFI NO], RECEIVED, TARGET, RESPONDED, STATUS, COMMENTS, [CCBA RESPO]
FROM [RFI/AD3/CIVIL]
WHERE STATUS = "PENDING"

On a related note, your table and field names reflect some less than desirable naming choices. We try NOT to incorporate spaces and non-standard characters like the / in names. They can, and often do, create complications. It's better to avoid them.
Highlighted

You have a much more serious design problem in that each department is in a separate table. Unfortunately, queries become significantly more complex when you do that. 

You COULD, as a work around, create a UNION query that includes each of the currently existing tables. That would have to be modified from time to time to accommodate changes in departments, though. 

 

I strongly urge you to correct this design problem now, rather than continue to invest extra work and complicated queries and code to compensate.

 

Merge all of them into ONE table, similar to the current one shown in your screen shot, with an additional FIELD in that table to indicate which department that record applies to.

Highlighted

@George Hepworth 

 

Thanks for the solution