MS Query for Using IF ELSE condition to create a table when source table is empty (1 Viewer)

Satya69 · Jul 1, 2019

I am using MS Access 2013.

I have a query which takes data from other query table.

I need to write the MS Query using a condition, say if the source table is empty, then create a table with set of columns, else create a different table with the values from source table.

I was trying to do it with IF NOT exists and then execute the conditions, but I could not do it.

MS Query is not simply taking if condition. it is unlike SQL server.

I am very new to MS Query, please do help me. thank you in advance.

Regards,
Satya

plog · Jul 1, 2019

In Access the place where you generally write queries, can only execute SQL. When we need to do scriptin other than SQL we use VBA. So, you can achieve what you want in Access, its just going to be written in a Module object, not a Query object.

However, why? Generally you don't save duplicate data. When you want a subset and/or transformation of an existing dataset you should build the query and reference it. Why do you need to actually create tables for this data? What's the bigger picture?

Satya69 · Jul 1, 2019

Thank you for the explanation

The actual picture is briefed below:

So I have table A, which is the source table. Then I have table B, which I use values form table A and do a pivot to make all the rows of a column in table A into columns in table B.

Sometime, Table A is empty and that is when table B ends up without a structure since there are no values in table A (so pivoting doesn't work right). This results in failure of execution of other following queries in the sequence.

My approach for solution was - Put an IF condition and see - If table A is empty then create a table with the required columns, so that the future queries will not fail. Else when table A is not empty, go ahead with the pivoting.

Please correct me if you have a better plan to tackle this issue from MS access Query perspective.

I would be grateful for your help!

theDBguy · Jul 1, 2019

Satya69 said:
So I have table A, which is the source table. Then I have table B, which I use values form table A and do a pivot to make all the rows of a column in table A into columns in table B.

Hi. Just curious... Have you tried using a Crosstab Query instead?

Satya69 · Jul 1, 2019

Hey Hi,

I am using pivoting and the problem arises only when the source table is empty, because the rows in the source table are pivoted as columns in the table B, which is later used by other queries referencing the column names.

Crosstab would not help here, my issue is when I get empty source tables, the queries stop running in the middle.

As plog suggested, I should write this If condition in VBA and call the VBA module in the MS query. Will that work?

theDBguy · Jul 1, 2019

Satya69 said:
Hey Hi,

I am using pivoting and the problem arises only when the source table is empty, because the rows in the source table are pivoted as columns in the table B, which is later used by other queries referencing the column names.

Crosstab would not help here, my issue is when I get empty source tables, the queries stop running in the middle.

As plog suggested, I should write this If condition in VBA and call the VBA module in the MS query. Will that work?

Hi. I don't know but I think a Crosstab Query could still work if you use the Column Headings property to define the required columns. You might give it a try and let us know how it goes. Just a thought...

plog · Jul 1, 2019

I provided no solution and questioned the entire method for this.

Why must data be saved to a table? Seems like an unnececssary hack to achieve what you want. I think SELECT queries are the way to go, not MAKE TABLE queries.

Satya69 · Jul 1, 2019

Yeah, SELECT queries will do, It was my mistake I told it as create table.

plog · Jul 1, 2019

So big picture this. What are you doing with this data once its "created"? What's the ultimate object of this task?

Satya69 · Jul 1, 2019

Once the data is created, then series of queries runs and give multiple excel reports at the end.

In this case of empty source table, all the queries should run and return no records in the final output file.

plog · Jul 1, 2019

Pivotted data should be the final datasource, not an intermediate step. Those final queries can be cross-tabs, but the one we are discussing should not be.

A cross-tab takes values and turns them into column names--which is a big no-no for data storage or sub-queries. So, I think you need to reconfigure your final queries to work on a proper data set.

Most likely this query we are tallking about will not be pivotted data, but a SELECT that contains the data you need. Then the final queries will be cross-tabs built upon this query.

Satya69 · Jul 1, 2019

Yeah, I get it now. I am going to use Select to contain the data I need. Thanks for clarifying on that

My doubt is, I want to write a MS query where it checks Table A whether it is empty or not and execute the conditions.
If it is in SQL Server, I would have written something like this.

-------
If Count (*) of table A is 0 then

Select a1,a2, a3, a4 from table C

Else

Select b1,b2,b3, b4 from table A

END IF

----

I could not do the same in MS query, I started working on MS Access two days back

Any advise on this? thank you

The_Doc_Man · Jul 1, 2019

Try something like this in VBA

Code:

If DCount("*", "[TableA]" ) = 0 then
    sSQL = "Select a1,a2, a3, a4 from tableC"
Else
    sSQL = "Select b1,b2,b3, b4 from tableA"
END IF
CurrentDB.Execute sSQL

Obviously, some embellishments are possible with things like error handlers and such, but this is how you would do this in VBA from a module.

plog · Jul 1, 2019

In this general form this is just wrong. If you have essentially equivocal fields in seperate tables you are storing your data incorrectly. By doing so you are using the table name to differentiate data. Instead your data should all be in the same table and you should use a field to differentiate data.

Can you give more specifics of TableA and TableC

Satya69 · Jul 2, 2019

Hi Plog,

You are correct, table C is a dummy table, which will be used to populate the columns for another table in order to run the series of MS queries without any error.

because when table A is empty, the rows will not have any values, so at that time we wont get the columns as desired (because few row fields in Table A are converted to columns in the new table) please see the below:

Here is Table A look like-

Employee name | Sequence | field | L1|L2|L3|L4||

675 | 1 |External code |
675 | 1 |Units |
675 | 1 |start date |

Table C -

Employee name | Sequence |External code | Units | start date||||

This helps? or please do let me know if you need more information

Satya69 · Jul 2, 2019

Hi The Doc Man,

Thank you for the VBA code. I have all the queries in MS query, so I was thinking, it will be easy if I incorporate the same condition inside an existing MS query than in a VBA module.

Between, if I am writing this piece of code in VBA, how will I place this VBA script in the middle of sequence of MS queries?

Could you please give me an approach. thanks!

sorry, I may sound dumb, because I am very new to MS access!

Satya69 · Jul 2, 2019

Hi The Doc Man,

Thanks for the VBA code.

Since all my table related code are in MS queries, I was thinking it would be easy for me to incorporate the same code in the existing MS query instead of a new separate VBA module.

Also can we refer/call this VBA module in the MS query. because I dont know how to bring in this piece of vba code into the process, where rest of the data and table select, insert. etc. functions are written in MS query. So this VBA code will be an odd man out in the plan.

I may sound dumb, I am new to MS access, please advise me with the approach. thank you.

plog · Jul 2, 2019

Sounds like a lot of unnecessary steps. Not certain why all your data isn't in the same table to begin with. Can you post sample of the 2 tables you are discussing?

The_Doc_Man · Jul 2, 2019

I was thinking it would be easy for me to incorporate the same code in the existing MS query instead of a new separate VBA module.

Unfortunately, that isn't the case because SQL isn't really oriented to this kind of testing. SQL has a limited number of verbs such as SELECT, DELETE, UPDATE, or INSERT, plus the verbs associated with DDL (none of which would help here.) It has a limited number of modifier clauses such as FROM, WHERE, ORDER BY, etc. To the best of my understanding, there is no syntax that works in the context of a FROM clause to allow for an alternation of attention from one table to another.

That is WHY we have VBA - because SQL can't do it all every time.

can we refer/call this VBA module in the MS query

You can refer to public VBA functions in place of fields in a SELECT or WHERE clause but I have never seen a function in other parts of the syntax of SQL. No, I don't think you can do this entirely from SQL.

Having this being an "odd man out" is because what you are trying to do is also an "odd man out" situation in normal processing such as is done in SQL. You are trying to divert a datastream from tableA to tableC and I don't know how to do that in ANSI SQL 92 syntax.

MS Query for Using IF ELSE condition to create a table when source table is empty (1 Viewer)

New member

Banishment Pending

New member

I’m here to help

New member

I’m here to help

Banishment Pending

New member

Banishment Pending

New member

Banishment Pending

New member

Immoderate Moderator

Banishment Pending

New member

New member

New member

Banishment Pending

Immoderate Moderator

Similar threads

Users who are viewing this thread