spreadsheet->access->spreadsheet

[grndpa]

To paraphrase Lou Costello "This is another fine mess I've gotten myself into!".

I have a multi-tab excel spreadsheet. Each tab appears to be highly normalized and thus lends itself to import into Access Tables. However, I think I would need to add keys and set up the relationships.

Since we will be going through multiple tests, I will need to repopulate this resulting database. Will I need to delete, define and massage the database for each testing iteration or can I simply filter the import to replace and insert?

I will also need to export this to the destination system -- the media / form is to be determined.

Boring Background Stuff ...

My company assigned me to handle the technical and mapping issues in converting from one commercial payroll system (AON) to another (ADP). I'm a mainframe guy though I have dabbled in vb.Net as well as mySQL using PHP.

These are not options for this conversion. Instead, what I have to work with is Microsoft Office 2003 -- of which I am basically illiterate.

Regards,

Grandpa (grnadpa) Brian

 

[SkipVought]

Hey grandpa, from one himself!

Using MS Query to get data from Excel faq68-5829

If you're most comfortable in Access, suck the data into Access. I am personally an Excel, guy, and from Excel can query data from all over the place and then massage it in Excel, which is much richer in analysis and reporting than Access.

I think that I might use both, faced with your task.

Skip,

_[red][/red]

 

[grndpa]

Hi Skip;

Great link.

Not sure I understand how this addresses my issue. I have multiple tabs. Multiple rows on one tab relate to a single row on another tab.

Before I invest in learning about queries, are these one-to-many relationships among tabs something I can do without a database?

 

[SkipVought]

"I have multiple tabs."

Each tab can be considered a TABLE in a database.

"Multiple rows on one tab relate to a single row on another tab."

Thats no problem. Although there is no "relationship" property that joins tables as in Access, your queries can join explicitly on those implied relationships.

I have a project that I am currently working on that queries data from DB2 & Oracle into various tabs in my workbook. Then I join the data from those tabs in a UNION query, but I could just as well join on common data fields, if the requirements warrranted it.

Here's an example of the code that I use, as this is an interactive application...

Sub GetSFC_ChartData()
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".XLS;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT"
    sSQL = sSQL & "  PM_PN As CHT_PN"
    sSQL = sSQL & ", PM_NG AS CHT_NG"
    sSQL = sSQL & ", #" & Format(wsParms.[WeekOf], "yyyy/mm/dd") & "# As CHT_Mon"
    sSQL = sSQL & ", 'I'         As CHT_Cat"
    sSQL = sSQL & ", ''          As CHT_Trv"
    sSQL = sSQL & ", ''          As CHT_Lat"
    sSQL = sSQL & ", Sum(PM_OH) As CHT_QTY"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`qtPM$`"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "GROUP BY"
    sSQL = sSQL & "  PM_PN"
    sSQL = sSQL & ", PM_NG"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "HAVING (Sum(PM_OH)>0)"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "UNION"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  PARTNO_201"
    sSQL = sSQL & ", NETGRP_275"
    sSQL = sSQL & ", IIF(RQ_DTE<Date(),CDate(INT((Date()-2)/7)*7),CDate(INT((RQ_DTE-2)/7)*7))"
    sSQL = sSQL & ", IIF(RQ_DTE<Date(),'P','D')"
    sSQL = sSQL & ", ''"
    sSQL = sSQL & ", ''"
    sSQL = sSQL & ", sum(RQQTY_275)*-1"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`qtRQ$`"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "GROUP BY"
    sSQL = sSQL & "  PARTNO_201"
    sSQL = sSQL & ", NETGRP_275"
    sSQL = sSQL & ", IIF(RQ_DTE<Date(),CDate(INT((Date()-2)/7)*7),CDate(INT((RQ_DTE-2)/7)*7))"
    sSQL = sSQL & ", IIF(RQ_DTE<Date(),'P','D')"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "UNION"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  PARTNO_201"
    sSQL = sSQL & ", NETGRP_285"
    sSQL = sSQL & ", CDate(INT((RP_DTE-2)/7)*7)"
    sSQL = sSQL & ", 'S'"
    sSQL = sSQL & ", ORD"
    sSQL = sSQL & ", iif(MRP_DTE<RP_DTE,'LAT','')"
    sSQL = sSQL & ", Sum(RPQTYORD_285)"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`qtRP$`"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "GROUP BY"
    sSQL = sSQL & "  PARTNO_201"
    sSQL = sSQL & ", NETGRP_285"
    sSQL = sSQL & ", CDate(INT((RP_DTE-2)/7)*7)"
    sSQL = sSQL & ", 'S'"
    sSQL = sSQL & ", ORD"
    sSQL = sSQL & ", iif(MRP_DTE<RP_DTE,'LAT','')"
'
    With wsSFC_ChartData.QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
        Application.DisplayAlerts = False
        .ResultRange.CurrentRegion.CreateNames True, False, False, False
        Application.DisplayAlerts = True
    End With
End Sub

Skip,

_[red][/red]

 

[SkipVought]

I wanted to add that in Excel SQL, the sheet tab qtRP is coded `qtRP$`.

If you want to proceed down this road, let me know.

Skip,

_[red][/red]

 

[lameid]

Since we will be going through multiple tests, I will need to repopulate this resulting database. Will I need to delete, define and massage the database for each testing iteration or can I simply filter the import to replace and insert?

In VBA you can use "docmd.transferspreadsheet" to import a spreadsheet to a given table by specifying its options. And you can use queries to filter and instert etc. But without knowing the general steps of what your are trying to accomplish it is hard to say how to go about it.

I disagree with SkipVought that Excel is better overall for reporting; Access definitely is. Excel is better at temporary adhoc data arrangement and therefore faster for somethings. It is also better at charting.

In Access you can define relationships... From the tools menu select relationships. Right clicking in access is generally a good thing to try for context menus and it will help here for adding tables. Dragging a field to another will create a relationship and you have options to set. If by chance you are at all serious about using Excel to do database analysis on normalized data, you should take a look at the vlookup function but at best using it is anal compared to writing a query in Access. I suspect that Access is the write tool as I believe you do since you posted in an Access forum.

If you can give me a quick overview of what specifically you want to do, I am sure I can at least tell you what you want to lookup.

 

[grndpa]

Thanks folks ...

What I was trying to do was get a feel for how big a mess I've gotten myself into.

Looking at microsoft's tutorials, it appears that "Action Queries" appears to be the most appropriate tool for this. According to the tutorial, I can update, insert, delete rows and even create tables within Access.

So in lieu of a persuasive reason to go with a spreadsheet, Access appears to have more promise.

Do either of you have a recommendation on a good tutorial for advanced queries.

And would either of you consider a fee-based retainer in case I get stuck?

Regards,

Grnadpa

 

[lameid]

I would be more than happy to help in a professional capacity but unfortunately the forums do not provide a way for us to exchange information and it is generaly frowned upon.

If you open a query, you can switch to the SQL view by droping down the button on the toolbar that typically toggles between datasheet and design view (far left button). You can then post your SQL in a message in forum701 and a question and schema information if necessary and someone is bound to help.

As for tutorials... None come to mind but Jet SQL is not all that different from ANSI-92 SQL so you may already have some idea how to do advanced queries from the checkered background you have listed. Personally I love the "Access <version> Developer's Handbook" series Published by SYBEX. I have the 97 and 2000 versions of them and they were invaluable to me learning Access.