Query to ignore blank cells in table (1 Viewer)

[Shaunmac]

Hello
I am looking for a way to pull data from a table based on the relevant field in the table below.

The issue is certain services within my data can only be searched by a specific fields. what i am trying to do is run a query on multiple fields (as below) but ignore the blank fields.
This table looks at another table Maindata1 i did link the relevant fields from below to Maindata1 but get blank returns

I would like to search from a form by service and then return data base on the none blank field. currently i search by Specialty01. but when specialty01 is blank i need to use the next field (trtmt_func) then if blank (Appttype01) and so on.
Being a complete novice on SQL i need serious help.
Any Help with this would be appreciated. That said i would like the query to save the data to a table with the name of the service from this above table.

thanks in advance

 

[theDBguy]

Hi. Based on the data you posted, what would be the actual result you want to see in the new table?

 

[Pat Hartman]

There seems to be something wrong with the schema. The columns to the right of Speciality seem to be a repeating group. Can you post the tables?

PS - tables do NOT have cells. They have columns and rows.

 

[Shaunmac]

i have a form that a user selects there service. this will trigger a query to provide a criteria to interrogate my main data to provide an output.
if i link all fields on my test table to OUHdata table i get a blank output. if however i only link specialty i get an output for any field with specialty filled in.

my issue if i select breast then specialty in the table above is blank. so i want to then use the criteria that isnt blank ( TRTMT_Func) and so on.
Line 21 would have to use criteria location01.
every time somone adds to the above table the criteria could be any of the other fields and not specialty.
it only works for specialty when used as below.

As an add on once the query works above i want to save the data as a table but using the service name from Tble_SPec_trigger

i hope this claers things up

 

[Shaunmac]

There seems to be something wrong with the schema. The columns to the right of Speciality seem to be a repeating group. Can you post the tables?

PS - tables do NOT have cells. They have columns and rows.

This is the table. a form is used to select the Service. from there i need the criteria in my query to be Specialty unless Specialty is blank, if blank then try TRTMNT_Func01, then APPTTYPE01 and so on.

I cannot post the other table for data quality reasons but i have posted the fields in the image within my post.
If i like more all the fields to the main data i get a blank output. so am a bit lost



Tbl_Spec_Trigger Tbl_Spec_Trigger

ID	Service	Specialty01	TRTMT_FUNC01	APPTTYPE01	Location01	Table
1​	Neurology	Neurology				Tbl01_Neuro
2​	Nephrology	Nephrology				Tbl02_Neph
3​	Neurosurgery	Neurosurgery				Tbl03_Neurosurg
4​	Neurosciences	Neurology				Tbl04_NeuroScience
5​	Breast		Breast			Tbl05_Breast
6​	Upper GI			Upper GI*		Tbl06_UpperGi
7​	BIU	Medical Microbiology				Tbl07_BIU
8​	Gastro	Gastroenterology				Tbl08_Gastro
9​	Ophthalmology	* Ophthalmology *				Tbl09_Ophthal
10​	Optometry			* Optom*		Tbl10_Optom
11​	Oral	Maxillo Facial *				Tbl11_Oral
12​	Orthopaedics	Trauma and Orthopaedics				Tbl12_Orthopaedics
13​	Orthodontics	Orthodontics				Tbl13_Orthodontics
14​	OCE	"Rehabilitation" Or "Allied Health Professional Episode"				Tbl14_OCE
15​	Ophthal VAS	Ophthalmology				Tbl15_Oph_VAS
16​	Ophthal Photo	Ophthalmology				Tbl16_Oph_Phot0
17​	Osteotomy	Orthodontics				Tbl17_Osteomy
18​	Respiratory Cystic Fibrosys	Respiratory Medicine				Tbl18_Cysticfib
19​	ENT	ENT				Tbl19_ENT
20​	Plastics	Plastic Surgery				Tbl20_Plastics
21​	Paeds NOC				NOC-Paediatric Outpatients	Tbl21_PaedNOC
22​	Paeds JR	Paediatrics				Tbl22_PaedJR

this give me output but only psecialty

i thought it should be like this but i cant get it to work

 

[theDBguy]

i hope this claers things up

Actually, it doesn't; but, it did provide some clarity. I was asking you to show us what the resulting table would look like after you run your desired query. I wanted to see the data again after it transformed from what you posted earlier into what you want or expect it to be when you run your query.

 

[Shaunmac]

Actually, it doesn't; but, it did provide some clarity. I was asking you to show us what the resulting table would look like after you run your desired query. I wanted to see the data again after it transformed from what you posted earlier into what you want or expect it to be when you run your query.

i expect something like this.
this is BIU as a service as you can see the specialty is Medical Micro.

However if i select breast there is NO specialty so i need the criteria to automatically move to the next field (column and row ) is not blank

 

[Pat Hartman]

That isn't the way SQL works. You are going to have to normalize the table to get this to work. You can use a union query to make the other three columns "equal" to a Speciality.

 

[Shaunmac]

Having just looked up Union query i cant see how i can achieve the goal i am after.

if i use the example optometry line 10 as an example the specialty is "allied health professional"

However i get multiple appointment types most of which are NOT optometry related
however

I have anonymised my main data and posted it below.


the reason i need to define each service is because the end user will not know how there data is recorded within our system just by specialty

I need some way to select my service and only pull the appropriate data. my current database uses multiple queries 5 for each specialty and 3 or 4 tables to produce the information they require. this is cumbersome and due now to the volume of users needs to be slicker.
I was looking for 1 set of queries for all services to reduce the database size and my time. I am not a programmer and did this as a hobby and it has developed into something used widely now over 50 users

i am open to any guidance

 

[Pat Hartman]

The problem is that the table is not properly normalized. You need to use a union query and that will get all the four columns into the same column. Upload a database with the test table and one of us will build the union query for you.

 

[arnelgp]

However if i select breast there is NO specialty so i need the criteria to automatically move to the next field (column and row ) is not blank

i think you need VBA and Recordset with it so you can bring "all records" for the specific "service".
then move through the recordset to see which record has fields (on either specialty, trt.., apptype) that is not blank.

 

[Pat Hartman]

OK, you can make the union yourself. Here is the pattern:

Select fldA, fldB, fldC, fldD, fldE
From YourTable Where fldC is not null
Union Select fldA, fldB, fld1, fldD, fldE
From YourTable Where fld1 is not null
Union Select fldA, fldB, fld2, fldD, fldE
From YourTable Where fld2 is not null
Union Select fldA, fldB, Fld3, fldD, fldE
From YourTable where fld3 is not null;

Then, use this union query instead of the table in whatever query you write. Just note that the data will not be updateable because the union query is not updateable.

Notice how the three unnormalized fields each replace fldC. What you end up with is a list of all values for fldC plus additional rows for each value in one of the other three columns. The critical thing about a union is that all select queries MUST select the same number of columns AND all the rows of a column must contain data with the same data type. Column names are irrelevant but they are taken from the first Select statement. There is no logic check. If you make a mistake in one select and choose address instead of last name, then the rows will have a mixture of last name and address in the "last name" column. Access doesn't care but your resultset will be worthless. So, sequence of columns is relevant.

If you don't want to include all the selects inside the union because that can make the union hard to work with, you can create individual querydefs and select the querydef. Just make sure that each querydef has the correct columns and selection criteria.
Select qry1.* From qry1
Union select qry2.* from qry2
Union select qry3.* from qry3
Union select qry4.* from qry4