473,883 Members | 1,614 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

CrossTab Query three tables

171 New Member
I am trying to connect three tables in a crosstab query.
Tbl-1 Field - Staff
Tbl-2 Field - Document
Tbl-3 Fields - Staff Document Viewed
What I am trying is to get a crosstab query or Vb module which can list all the staff from Tbl-1 as the row heading and all the Documents from Tbl-2 as the Column heading and put "Yes" or "No" if the staff viewed the document which is available in Tbl-3
Much appreciated if someone can help me

Sep 28 '12
36 3694
12,516 Recognized Expert Moderator MVP
rajeevs, there's no need for code, you just need to use a left or right outer join instead of the inner joins that you're using. It's a very simple change.
Oct 3 '12 #11
5,501 Recognized Expert Moderator Expert
I don't understand how you are not able to get the query to work using either Rabbit's or my approach.
I just tested the approach I gave you on one of my test databases... worked very well.
I'm currently playing with the SQL you posted in #8 to see which one(s) to make left/right joins on... if Rabbit said it can be done, I suspect that it can....

Please read the posts again and post your efforts.
Oct 3 '12 #12
5,501 Recognized Expert Moderator Expert
Tweaking SQL in post 8 every which way and all I get is errors from msaccess.
However, If I do a cross-product to expand staff against documents and then relate this back against the viewed documents on the staff_pk and document_pk then I can return exactly what OP wants... all staff in the rows and all documents in the columns with a single yes/no/blank at the intersections.

Easier method?
Oct 4 '12 #13
12,516 Recognized Expert Moderator MVP
The only thing that I see that may be causing errors is the parentheses they're trying to use. But if you reorder the joins a little bit, and get rid of the parentheses, I think it should work by doing a table1 left join table3 left join table2.
Oct 4 '12 #14
5,501 Recognized Expert Moderator Expert
Thnx Rabbit: I thought I had tried that and MSA tossed an "Ambiguous Outer Joins…" However, the test DB I built is on the home PC while listening to the children try to bathe each other (2x3yr+1x9yr)=c haos+flooded_ba throom… I kind-of had to go put a stop to that before the bathtub floated down the street!
I'll give that rearrangement a shot when I get home...
Oct 4 '12 #15
171 New Member
Dear Zmbd

Can you send me your test DB so I can have a look and try to build the same way you did.
Thank you
Oct 10 '12 #16
5,501 Recognized Expert Moderator Expert
I'll pass that along this evening... sorry the DB is at home :)

In the meantime, will you please post what you have been working on to solve this?

(While I'm at it: Rabbit, tried the suggestion as given in #14, that doesn't seem to work)
Oct 10 '12 #17
12,516 Recognized Expert Moderator MVP
I'll wait till you post your sample db as I'm too lazy to mock one up right now lol.
Oct 10 '12 #18
5,501 Recognized Expert Moderator Expert
Just waiting for rajeevs to post the most recent effots... otherwise the information in post 9 is how I built the DB for the cross-product-to-hasread queries.
However, the second paragraph in 9 didn't work quite as expected... stupid jet.
Oct 12 '12 #19
171 New Member
No luck so far. I am stuck with another issue in another db. I will be posting that because it is very critical for me at the moment. But to this issue so far i can make a report based on viewed staff and documents. Main issue is still pending
Hope you will give me your test DB.

Thank you
Oct 12 '12 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Matthew Wells | last post by:
I have a crosstab query based on anothe query. The base query resultset has no null values in its "Quantity" column. However, when I create the new crosstab query from the base query, the records that should be 0 are blank. I trid the old IIF(IsNull(....etc but that didn't help. How do I make my blanks 0s? Thanks. Matthew Wells MWells@NumberCruncher.com
by: Kaur | last post by:
Hi, I have created a report based on a cross tab query and would like to Sort the column heading of the report based on the sort order of another field. I have three tables called survey results, survey questions and survey response choices. Survey response choices has over 800 choices types. Survey results capture results of all the surveys and has questions, respondent, response choice, response, responsechoice order which
by: Nenad Markovic | last post by:
Hi everybody, When executing a Crosstab Query I see only rows (defined in a row heading) that have values (defined in value field) in at least one column (defined as column headings). How can I make a Crosstab Query that shows all rows, regardless they have values in column headings. For instance, the result that I get now is like this
by: jason.teen | last post by:
Hi All, I am having trouble creating a crosstab query. In my original data I have two columns, One called "Categorized" and one called "Mapped' in which those columns can hold values of "true" or "false" only in checkbox format Ie.
by: John | last post by:
Hi I have three tables with a common id with which they can be linked. I need to merge them in a way that the resultant table has all records from three tables. Below is what sort of result I am expecting; Table 1 ID Value1 1 A1
by: Peter Herath | last post by:
I want to create a dynamic report using a crosstab query...... pls someone look into my attached example database and help me out to do the report generation.... example is like dis...: there are 4 combo boxes.. when u select an item from each combo box and click the ViewReport Button then those selected item values should go as parameters to the crosstab query in the vba code subroutine and from that the report should be generated. pls...
by: Peter Herath | last post by:
I want to create a custormizable report . For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go to the crosstab query as parameters and execute the query. upto that i have done my coding part but...
by: m.wanstall | last post by:
I have a crosstab query that compiles data for Months of the year. I have a stacked select query on top of that crosstab query that uses the latest 2 months data and exports it to a fixed length flat file. Ideally I would like to be able to just select the Last 2 Columns of the Crosstab query as inputs to the Select query WITHOUT having to go in month after month and manually change it... I may be asking a bit much here but is there a...
by: keyur1719 | last post by:
Hi, I have a crosstab query which is based on a simple select query. Here is how the query works.. The base query gets it date from employee table and their incentives table for the given quarter. This query feeds to the Crosstab query for displaying the quarterly data. I have a field in the crosstab query which calculates the sum of all three months in the quarter. I want to give ranks to this quarterly total in the crosstab query. I...
by: NigelBrown | last post by:
Hi All, I have written the below UNION QUERY (I know its wrong but trying to show what I want to achieve) I would like all three tables to convert to one - this is only a one off every day and the result is about 400,000 rows combined. It works with two tables fine, I am not sure how to go about including the third SELECT, when I run the below code i get the error "Too many fields defined". Any help/advice would be greatly appreciated. ...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.