Moving a column or making it equal to another (1 Viewer)

ivan_a · Nov 16, 2017

Hello,

I am trying to import 2 excel generated reports into ms access tables and once they are imported I want to make a union query out of the two. The problem I have is that two of the fields in report1 are different than report2 so my union will not work. I need to make one of two things 1) Move column [Effective Date] as the 6th column in my table and delete [future term date] OR make column [future term date] = [Effective Date] and than delete column [Effective Date].

I am able to delete [Effective Date] with this line of text in my function:

Dim Removefield3 As String
Removefield3 = "Alter Table tblOTENoLonger DROP [Effective Date] AutoIncrement"
CurrentDb.Execute Removefield3

But I need to either make [future term date] = [Effective Date] before the deletion happens OR move [Effective Date] to be the 6th column in my table.

Any ideas would be much appreciated !

arnelgp · Nov 16, 2017

i think you do not need to delete the column. union query must have same number of columns, the columns on both tables need not same datatype.

bob fitz · Nov 16, 2017

Does each row of both tables have a unique identifier

isladogs · Nov 16, 2017

When doing Union queries, add dummy fields to one or more parts of the query to make the number and order of columns the same

e.g. add '' AS X as the dummy field before the one you're referring to

ivan_a · Nov 16, 2017

Thank you for the replies, to your points:
ridders
arnelgp- I was not clear, in that one of the reports has one additional column, so that is why I want to get rid of it.
bob fitz - sadly no, the rows contain dates which can be the same
ridders - I could add a dummy fields, but I would later want to export the query back to excel and I would prefer to keep it clean for that.

arnelgp · Nov 16, 2017

you add only dummy field to query not to table.

ivan_a · Nov 16, 2017

arnelgp said:
you add only dummy field to query not to table.

That would still not solve my problem in full. Fundamentally, I have one column in report 1 called [future term date] and two columns in report 2 called [effective date] and [future term date]. What I want is for my Union query to take [future term date] from report 1 and [effective date] from report 2. The only way I see this happening is if I either make [effective date] = [future term date] in report 2 OR change their places.

arnelgp · Nov 16, 2017

does these 2 reports came from same excel, all same except for the outputted dates? if yes the its Join query you need and not union.

ivan_a · Nov 16, 2017

arnelgp said:
does these 2 reports came from same excel, all same except for the outputted dates? if yes the its Join query you need and not union.

These reports contain the same information, but for different people, so I need to union them so that I get the full list of people in both reports. However, report2 has one additional field, which contains information that corresponds to another field in report 1.

Mark_ · Nov 16, 2017

ivan_a,

Make a query on table 1.
Make a query on table 2.

Add a dummy fields to the query that has less fields than what you want.
Use an expression to rename the data you want. In the query designer it would look like

Code:

[effective date]: [future term date]

Then you can do your union based off of the queries you created.

ivan_a · Nov 17, 2017

Mark_ said:
ivan_a,

Make a query on table 1.
Make a query on table 2.

Add a dummy fields to the query that has less fields than what you want.
Use an expression to rename the data you want. In the query designer it would look like

Code:

[effective date]: [future term date]

Then you can do your union based off of the queries you created.

Thank you for the suggestion, I will probably go that route, was just hoping there is a way of avoiding adding too many new queries.

isladogs · Nov 17, 2017

You don't have to add any new queries.
It can all be done as code in the VBE if you prefer

Pat Hartman · Nov 17, 2017

The Union query is a series of select queries. Those queries can be saved as querydefs or embedded in the Union. You are in control over what columns are selected and what order they are selected and you can skip columns entirely in one table or add dummy columns if one of the tables has fewer columns than the others and you want the extra data.

There are two basic rules for unions
1. All the select queries MUST include the same number of columns
2. All the selected columns must match in data type so if the third column you select in query 1 is a date then the third column in all the other selects must be a date.

Colum name is irrelevant but the union will take the names from the first select as the column names so if you care what the column names are, use Alias' if necessary on that select statement.

The union cares only about data type, not content so you have to be careful. If you select firstname, lastname, address in the first select and address, firstname, lastname in the second select, the union will work fine. It just won't make any sense.

ivan_a · Nov 18, 2017

Pat Hartman said:
The Union query is a series of select queries. Those queries can be saved as querydefs or embedded in the Union. You are in control over what columns are selected and what order they are selected and you can skip columns entirely in one table or add dummy columns if one of the tables has fewer columns than the others and you want the extra data.

There are two basic rules for unions
1. All the select queries MUST include the same number of columns
2. All the selected columns must match in data type so if the third column you select in query 1 is a date then the third column in all the other selects must be a date.

Colum name is irrelevant but the union will take the names from the first select as the column names so if you care what the column names are, use Alias' if necessary on that select statement.

The union cares only about data type, not content so you have to be careful. If you select firstname, lastname, address in the first select and address, firstname, lastname in the second select, the union will work fine. It just won't make any sense.

Thank you Pat, that actually helped me solve my problem!

Pat Hartman · Nov 19, 2017

You're welcome. With understanding comes power

Moving a column or making it equal to another (1 Viewer)

ivan_a

New member

arnelgp

..forever waiting... waiting for jellybean!

bob fitz

AWF VIP

isladogs

MVP / VIP

ivan_a

New member

arnelgp

..forever waiting... waiting for jellybean!

ivan_a

New member

arnelgp

..forever waiting... waiting for jellybean!

ivan_a

New member

Mark_

Longboard on the internet

ivan_a

New member

isladogs

MVP / VIP

Pat Hartman

Super Moderator

ivan_a

New member

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread