Can this be done in either Access 2K or Excel 2K?

  • Thread starter Thread starter John S
  • Start date Start date
J

John S

I need to merge two reports coming from two different databases. The
reporting tool that I use allows me to export the report to a csv file,
which allows me to work with it in either Excel or Access. I need to pull
from two seperate databases since one gives me the employee roll ups, the
other one allows me to pull sales by outlet. The joins in the database that
I pull the sales from are not great, so I can only pull outlets with sales.
Any outlets without sales fail to show up on this report and the roll ups
are not available in this database. Here are the basic report columns that
I am pulling:

1st report:

Mgr | Employee | CompanyName | CompanyID | OutletID | OutletName

2nd report:

OuletID | Sales

The first report is a roster of all Outlet ID's, where as the second report
only contains Outlet ID's with sales.

What I need to do is merge the reports so the columns are like this:

Mgr | Employee | CompanyName | CompanyID | OutletID | OutletName | Sales

The problem that I am encountering is that when I import the data from the
second report into the first report, the data from the first report shows
up as extra rows with blank fields for the fields not in the first report.


Any ideas? By the way, I don't have administrative privileges on my PC, so
please don't refer me to a third party add on. Step by step instructions
would be most appreciated.

Thanks!
 
I need to merge two reports coming from two different databases. The
reporting tool that I use allows me to export the report to a csv file,
which allows me to work with it in either Excel or Access. I need to pull
from two seperate databases since one gives me the employee roll ups, the
other one allows me to pull sales by outlet. The joins in the database that
I pull the sales from are not great, so I can only pull outlets with sales.
Any outlets without sales fail to show up on this report and the roll ups
are not available in this database. Here are the basic report columns that
I am pulling:
Click to expand...

Access will work fine for this. One thing to bear in mind: these
aren't *reports*, they are sets of data - Tables.
1st report:

Mgr | Employee | CompanyName | CompanyID | OutletID | OutletName

2nd report:

OuletID | Sales

The first report is a roster of all Outlet ID's, where as the second report
only contains Outlet ID's with sales.

What I need to do is merge the reports so the columns are like this:

Mgr | Employee | CompanyName | CompanyID | OutletID | OutletName | Sales
Click to expand...
The problem that I am encountering is that when I import the data from the
second report into the first report, the data from the first report shows
up as extra rows with blank fields for the fields not in the first report.
Click to expand...

So don't Import one table into the other. Instead, import the two .csv
files separately into Tables; create a Query joining the two tables by
OutletID. With the default type of join - an Inner Join - you'll only
see records which exist in both tables.
 
import report1 into sheet1, and import repost2 into sheet2

On sheet1 fill col headed as "sales" =VLOOKUP(E2,Sheet2!A:B,2,FALSE)
Then set autofilter to block out #N/A (or you could fill in on sheet2 ALL
OutletID without anysales to 0)

HTH
Phil
 
This will give cleaner results with out filtering:

=IF(ISNA(VLOOKUP(E1,Sheet2!A:B,2,FALSE)),"",VLOOKUP(E1,Sheet2!A:B,2,FALSE) )
HTH
Phil
 
Access will work fine for this. One thing to bear in mind: these
aren't *reports*, they are sets of data - Tables.



So don't Import one table into the other. Instead, import the two .csv
files separately into Tables; create a Query joining the two tables by
OutletID. With the default type of join - an Inner Join - you'll only
see records which exist in both tables.
Click to expand...

Hi John,

I went with Phil's suggestion since I am much more knowledgeable on Excel
than Access. However I am going to attempt your suggestion as a more
permanent solution. I do want an outer join between the Outlet ID's in
both tables, not an inner join however since all Outlet ID's need to show
up, not just outlets with sales. Is this correct?

I was playing around with it today for a bit and I kept running out of
memory. I was also having problems sending email too, so I am thinking that
there might be something else going on. I wish that I could have forwarded
the files to my home computer to experiment with. Tomorrow's another day.

Thanks for your suggestion.
 
This will give cleaner results with out filtering:

=IF(ISNA(VLOOKUP(E1,Sheet2!A:B,2,FALSE)),"",VLOOKUP(E1,Sheet2!A:B,2,FALSE) )
HTH
Phil
Click to expand...

That did help. Immensely. Thanks!
 
Back
Top