Union Query - 3 tables 1

[romanzero]

Hi,

I need to make my relational database into an excel file.

In the database there is:

tblEmployees
tblJobHistory
tblDistributionActual

There is 1 Employee per JobHistory, but 1 + DistributionActual per JobHistory.

I need to make a "flat file" where for each Employee there is one record, one JobHistory, and an unlimited number of DistributionActual records. Everything must be on one line in a spreadsheet. I will need to generate a name for each of the Distributions (I know no one has more than 10).

I am sure that a Union Query will do what I need, but I can't seem to make it work.

Any help you can give would be greatly appreciated.

 

[JonFer]

What are the fields in tblDistributionActual? You can probably use a crosstab query and make the "Column Heading" an expression based on data in tblDistributionActual. Check out the crosstab information in help if you haven't used it yet and experiment with just tblDistributionActual until you get it how you want. Then add the other two tables.

 

[romanzero]

I thought about cross-tabs, but I don't think it will work.

There are about 1500 employees. Each has 1 - 10 Distributions.

There are over 500 possible distribtion choices.

I think a cross-tab would be too big.

But thanks for the idea...

 

[JonFer]

What are the fields in tblDistributionActual?

 

[romanzero]

Distribution_Tbl_ID
Account_Distribution
Amount
Job_History_Link



Each record from the table tblDistributionActual is linked to a record in the table tblJobHistory. Again there might be only one linked record, but some have more.

What I need as an end result would look something like this (picture an Excel spreadsheet)


EmployeeA, Distribution1, Amount1, Distribution2, Amount2
EmployeeB, Distribution1, Amount1
EmployeeC, Distribution1, Amount1, Distribution2, Amount2, .


I have to make my relational database into a flat csv file to be uploaded into a mainframe system. This is driving me nuts!

Thanks!

 

[JonFer]

Can an employee ever have two records with the same Account_Distribution value? If they can, you'll need to summarize the amount for each Account_Distribution value first. Then you can do this:

Select tbl_DistributionActual.Job_History_Lnk,
tbl_DistributionActual.Account_Distribution,
tbl_DistributionActual.Amount,
Count(da.AccountDistribution) as Rank
From tbl_DistributionActual Inner Join tblDistributionActual as da
On tbl_DistributionActual.Job_History_Lnk = da.Job_History_Lnk
Where tbl_DistributionActual.Account_Distribution <= da.Account_Distribution
Group By tbl_DistributionActual.Job_History_Lnk,
tbl_DistributionActual.Account_Distribution,
tbl_DistributionActual.Amount

This query will give a rank to each Account_Distribution value for each Job_History_Lnk value (i.e. each employee). The query can then be used in a crosstab with the rank as the "Column Heading" to get the Amounts for each employee in columns.

To get both the Account_Distribution and Amount in the crosstab requires another step but try out the above first and post back if you have questions.

 

[romanzero]

JonFer,

That is working quite well. Thank you.

Now I have the employee name and 1 - 10 columns with [ActualAmount].

How do I get [Account_Distribution] into the Crosstab while keeping only one line per employee?

 

[JonFer]

You can two things. The first is a "cheat" but is easier to do and the second is a SQL solution.

1. In the "Value" field for the crosstab where you currently have Amount, enter this:

Account_Distribution & Chr$(10) & Amount

Set the "Total" attribute to Min if you have it as "Sum" now. This assumes you are exporting to a tab-delimited file (Chr$(10) is a tab character but change it to "," if you are using commas as the delimiter) without column headings. It adds a tab between the two values in the crosstab "cell" so when you export the query it will appear to be separate fields. If you want the column headings, you have to do something similar with the "Column Heading" field.

... Or ...

2. Create a sequence table with 1 field and 2 rows in it containing the numbers 1 and 2. Add this to the crosstab query without any join lines.

In the "Column Heading" field use this:

Format([Rank],"00") & IIf (SequenceNum=1,"Amount","Distr")

In the "Value" field, use this with the "Totals" attribute set to Min:

IIf(SequenceNum=1,[Amount],[Actual_Distribution])

You can play with the column heading field until you have it sorting how you want automatically.

 

[romanzero]

JonFer,

I have never seen someone work so hard for a star!

Thank you very much. I used the SQL (option 2) and it works perfectly!

You must dream in SQL...

 

[JonFer]

Once I've started, I like to see it through. You picked up the idea for each step and got it working with a single post so it wasn't a problem.

By the way, posting the table structures and a few sample records helps a lot. If you're working with a query already, post the SQL and you'll get faster replies. Posting the desired output (which you did) is also good.