Anyone good with reporting/databases?

Discussion in 'Tech Talk' started by SKeefe, Jan 28, 2010.

  1. SKeefe

    SKeefe

    Messages:
    869
    Likes Received:
    0
    Joined:
    Mar 11, 2003
    Location:
    SC
    Quick question -

    I've got an accounting software (that is based on FoxPro and uses .dbf files) that forces you to "close out" payroll every year. When you do this, it separates the previous years data (bunch of .dbf files) into a separate folder - so that data is no longer accessible when you open the current "company."

    Thus, there is a payrec.dbf file (holds the payroll info) in the current "company" folder, a payrec.dbf file in a 2009 folder, one in a 2008 folder, and so on.

    Well, obviously sometimes you need a payroll report that covers more than one year. I've been trying to use Crystal Reports to do this but it is giving me errors.

    Anyone have any advice?
     
  2. Drjones

    Drjones

    Messages:
    6,157
    Likes Received:
    57
    Joined:
    Sep 28, 2002
    Location:
    CA, just outside the United States
    Switch to Quickbooks?

    I bought Quickbooks Pro 2009 from Staples for $69.00, including free overnight delivery just a few months ago....
     

  3. SKeefe

    SKeefe

    Messages:
    869
    Likes Received:
    0
    Joined:
    Mar 11, 2003
    Location:
    SC
    Not an option...
     
  4. edcrosbys

    edcrosbys

    Messages:
    978
    Likes Received:
    5
    Joined:
    Aug 22, 2007
    Location:
    VA
    Sounds like the software deals with different years by closing one year's database and starting a new one. :wow:

    You might want to see if there is a way to export the payroll data you need to a format that you can import into excel or access. Do this for the separate years, then run report from there.

    Just for giggles, are we talking FoxPro 2.6 or VFP?
     
  5. StuntPilot

    StuntPilot

    Messages:
    167
    Likes Received:
    41
    Joined:
    Oct 22, 2006
    Location:
    DFW, TX
    I agree with edcrosbys. See if the accounting software will export the prior year DBF files as CSV or comma delimited text files. Then create one big file for your historical analysis, and save it as an Access or Open Office database.

    If that option does not work, copy the DBF files to a new directory, and try to open them with Open Office Base, or a third party tool like DBFview. It has a free 30 day trial.

    http://www.dbfview.com/how-to-open-dbf.html

    http://www.openoffice.org/
     
  6. SKeefe

    SKeefe

    Messages:
    869
    Likes Received:
    0
    Joined:
    Mar 11, 2003
    Location:
    SC
    I appreciate the advice guys, I'm not sure why I didn't think of that. I'll try to work on it some today.
     
  7. SKeefe

    SKeefe

    Messages:
    869
    Likes Received:
    0
    Joined:
    Mar 11, 2003
    Location:
    SC
    That is correct.
     
  8. SKeefe

    SKeefe

    Messages:
    869
    Likes Received:
    0
    Joined:
    Mar 11, 2003
    Location:
    SC
    What I've got is a separate .dbf file for 2006, 2007, 2008, 2009, and now the current 2010 payroll files.

    I got the data from the 2006-2009 .dbf files and put it all together in one .dbf. So, this still leaves me working with two .dbf files in Crystal Reports (the 2006-2009 file and the current 2010 file) since I can't combine the current 2010 file with the old stuff since the 2010 file is constantly updating.

    Anyway, I'm not getting an error when I try to use both .dbf files in Crystal Reports now. But I am getting a bunch of duplicate records. For instance, every record for an employee in the 2006-2009 dbf will duplicate for every instance of a record for that employee in the 2010 dbf.

    What the heck?
     
  9. SKeefe

    SKeefe

    Messages:
    869
    Likes Received:
    0
    Joined:
    Mar 11, 2003
    Location:
    SC
    I think I am just having a problem understanding where in the report to put the fields from the second dbf file.
     
  10. StuntPilot

    StuntPilot

    Messages:
    167
    Likes Received:
    41
    Joined:
    Oct 22, 2006
    Location:
    DFW, TX
    Each year’s DBF file probably has a table inside that holds the unique employee records (Name, address, SSN), for example called ‘employee’. This info only needs to be saved once. The ‘employee’ table is linked to another table containing all the detailed payroll records. The payroll info can have numerous records linked to one employee record. The two tables are linked probably by a key like SSN or an employee ID.

    Check out the attached diagram. It uses patientID to link the static patient info to the various records in the other tables.

    Having the historical DBF file and the 2010 DBF file open in crystal reports means that the employee records will be duplicated…which you already realized.

    If you know the key field between the two tables, you can delete the duplicate employee records, leaving only the key field to link back to.

    Not knowing enough about your environment, I can only provide limited help.

    Backup everything before experimenting.
     
  11. SKeefe

    SKeefe

    Messages:
    869
    Likes Received:
    0
    Joined:
    Mar 11, 2003
    Location:
    SC
    There is a separate employ.dbf file with the employee info in it that you speak of, but as you can see, it is a separate file from the payrec.dbf file that I am using in Crystal Reports.
     
  12. StuntPilot

    StuntPilot

    Messages:
    167
    Likes Received:
    41
    Joined:
    Oct 22, 2006
    Location:
    DFW, TX
    What files are you opening in Crystal Reports?

    2010 - payrec.dbf and employ.dbf
    Combined years - payrec.dbf and employ.dbf

    If you are doing that, crystal reports is just warning you that the employ.dbf files will have duplicate records. The payrec.dbf file should have duplicate records because each employee will have multiple pay periods.

    If they are separate DBF files then you can try the following:

    Successive Year Files

    - Combine each payrec.dbf into one file, and leave duplicate records
    - Combine each employ.dbf into one file and remove the duplicate records
    - Keep the files linked by their current key field

    Now you have your combined prior year data, with unique employee records pointing to the multiple payroll records.

    Crystal Reports should be able to open the two files from above and compare them to the 2010 .DBF files.
     
    Last edited: Feb 5, 2010