Query not pulling through all the data?

[Sarnie83]

Hello

I am new to the forum and am a relatively new user of MS Access.

I have had to learn for work and have learnt everything so far through online tutorials.

I am currently trying to pull a query off from a table that needs to have 3 columns side by side, linking to the same ID number.

I seem to have managed to do this by making three seperate queries for each column I need, however, when I put it into one query, it is only pulling through the data form the second column (a total of 273 rows), when it should be pulling through the data from the first column (800+ rows).

The SQL looks like this if this helps:
SELECT [SM_Antennas_DL-1_Q].[Site ID], [SM_Antennas_DL-1_Q].[Antenna Type], [SM_Antennas_DL-1_Q].[Electrical DT], [SM_Antennas_DL-2_Q].[Electrical DT], [SM_Antennas_DL-3_Q].[Electrical DT]
FROM ([SM_Antennas_DL-1_Q] INNER JOIN [SM_Antennas_DL-2_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-2_Q].[Site ID]) INNER JOIN [SM_Antennas_DL-3_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-3_Q].[Site ID];

So the data DL-1, DL-2, DL-3 is from the same database.

Hope this makes sense to anyone......and thanks in advance!

 

[plog]

Can you provide sample data? I need 2 sets--1. starting sample data from your table and 2. what the ultimate resulting data should look like based on 1. Be sure to include table and field names and enough data to cover all cases. Use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name,...
Sally, 32, 1/2/2009
David, 49, 4/4/2010
Larry, 11, 5/5/2008

 

[Sarnie83]

The table name is SM_Planet_Report_LT and from that I have pulled
Site ID, Sector ID and Electrical DT

I have made these into three seperate queries. The data in the field "Sector ID" is either DL-1, DL-2 or DL-3, so have seperated these into the three different queries.

What I am trying to do is put them into three seperate columns against the same site ID.

I'm not sure if the initial 3 queries is the best step to pull all this data through like that or if there is an easier way?

 

[Brianwarnock]

Can there be upto 3 occurrences of the SiteID containing the various values for the SectorID

brian

 

[Sarnie83]

I've attached a file of what the data looks like and how I would like it laid out as it's easier for me to explain!

I am trying to pull the data through so that each individual Site ID has all the different antenna types on one line!

Thanks

 

[Brianwarnock]

One query something like, I've removed the Blanks in the object names , so should you the cause nothing but heartache.

Select SiteID, IIf(Sectorid=1,1,"") as Electrical_DT_DL-1, IIf(Sectorid=2,2,"") as Electrical_DT_DL-2,IIf(Sectorid=3,3,"") as Electrical_DT_DL-3
From SM_Planet_Report_LT

Brian

I am stupid of course this wont work

 

[Brianwarnock]

Your joining of the three queries does not work as all three do not contain all siteids , If each query contained the IIF as shown relevant to its requirement then all SiteIDs would be present in all queries, but I'm sure I've seen a better way to do this. hoever I cannot think of it and a working solution beats no solution.

Brian

 

[plog]

Sounds like what you need is a cross-tab query:

https://support.office.com/en-US/Ar...cc8-ba60-2cd8484667e8?ui=en-US&rs=en-US&ad=US

It basically turns one row of data into column headings. From the sample data you posted, the cross-tab will get you there.

 

[Sarnie83]

Thank you all for your replies. And thank you! The Cross tab Query is exaactly what I was looking for!! Sorry, I am very new to Access and sort of teaching myself as I go along at the moment. That's amazing thank you!