Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Angry Unanswered: Same Name Data won't print on report?

    I have a Db that has 3 tables Company, Employee, HepShots. When I use report wizard to generate a report; selecting Company, then it's employees, then the employees Hepshot records. The report will not print the second name if two employees have the same last name. It seems that only the "Detail section" is the only one that loops through the database. What am I soing wrong, can someone help??????

    Thanks..

  2. #2
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Hi,

    Try grouping by the primary key field from your employee table. Sounds like you are grouping them by last name, which would do just that...group all people with that name.

    Post again if that doesn't make sense.

    HTH,

    Chris

    P.S. If you want to post your db, I'd be happy to fix it for you too.

  3. #3
    Join Date
    Feb 2004
    Posts
    67
    Hey Chris,

    That worked fine, but now the names are not in alphabetical order. How do I get them in order? As for posting my DB, I am new to this and do not know how to compile or zip my db, then upload it to the dbform.

    My second problem is; I need to loop through the HepShot table in a report to get to the last record, so I can determine...If an employee needs a shot, when the shot is due, and what number the shot is?

    The way I have it now it lists all shots and information.

    thanks..

  4. #4
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Glad that worked. I'll try to answer your questions in order:

    1. Don't worry about compiling just yet. That's used to compile when you're using VBA code. (For future reference, Compile is under the Debug menu when viewing any VBA code)

    2. To ZIP the file, you would locate your database file, then a right click, then "add to yourdbname.ZIP". Must have WinZip installed for this (it's free at winzip.com) To post that file to the forum...use the "Manage Attachments" button when replying...it'll walk you through attaching.

    3. There are several ways to sort the report. Easiest being in Design View of the report. Then go to View Menu, then "Sorting And Grouping". If not already there, you can add a field to sort by using the drop down..in your case employee. This is also where you could add grouping headers/footers for any field.

    4. I'm a little confused about shot number. Do you need to count how many shots this person has had? If that's the case, you'd need to set up a query that selects the top 1 records when sorted by date. Then the shot number would need to be in a textbox using a calculation on the report...the DCount function is probably the way to go. If you're attempting this on your own, there is a help file on the exact syntax for that function.

    Again, if you're able to post it...I'd be happy to take a look. Otherwise, let me know if any instructions are unclear (haven't had my whiskey...err coffee yet )

    Chris

  5. #5
    Join Date
    Feb 2004
    Posts
    67

    Smile

    I am still having trouble? OK lets see if I can explain this.....

    First: My report is setup with a PAGE header, COMPANY header, EMPLOYEE header, DETAILs header, then PAGE Footer. In the COMPANY header is the "companyName" and the labels for the report (.ie Emplyee name, DOB, SSN, Shot Number, Date Given and Next shot Date.) In the EMPLOYEE header is the "EmployeeName", "SSN", "DOB", and "NextShot". In the DETAIL is the "ShotNum". so the report looks like this:
    " Company EmployeeName/SSN DOB Num DateGiven Nextshot"
    ----------------------------------------------------------------------
    ABC-Dist
    Doe, John 123-45-6789 12-12-12 11-12-04
    1 5-5-04
    2 6-5-04

    I need a report to list the employees information, next shot due and what shot is due. In the above scenerio:
    Employee Shot Due Next Shot Date
    Doe, John 123-45-6789 3 11-12-04

    I use the first report as a detailed report, but I need something scaled down report to send to the companies.

    Thanks for all your help Chris

    Chuck.....Enviva

  6. #6
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Ah, makes sense. I'll put a quick sample together for you and post it here. I'll try for this afternoon. I'm a little under the gun today but the whiskey is kicking in.

  7. #7
    Join Date
    Feb 2004
    Posts
    67
    Thank you!!...Have a double on me

  8. #8
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Chuck,

    NP. Here you go. Pretty sure this does what your looking for. I've left some comments for you in the reports. Essentially, what I did was created a 'totals' query that counts the shots per employee and adds 1.

    Let me know if you have any trouble with this. Sample is in Acc2000.

    I purposely left the 'ID' fields in the reports so you could better see what I did.


    Chris
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2004
    Posts
    67
    THANKS A MILLION.........I owe you a bottle of JACK-D
    Everything works fine....I actually have a field name "ShotNum" storing 1,2, or 3. I was trying to use that field to goto last record and then add(1) to it. Never thought of using the recordID and counting that. GREAT JOB......
    Thanks...Chuck

  10. #10
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    You're very welcome. Glad it worked out. I'd better pass on the bottle, my boss is starting to count my shots...sorry couldn't resist.

    Regards,

    Chris

  11. #11
    Join Date
    Feb 2004
    Posts
    67
    Hey Chris....well everything worked until I disected it more......One problem! Some employees started their series of shots at a different company, so when they got to company-ABC, they started their shots on number 2 or thier last shot #3, so counting the SHOTID causes an employee who gets his 3rd and final shot with his new company-ABC now gets his 2nd shot listed on the report, because he only has (1) record in the shotID.
    Any suggestions.....Chuck

  12. #12
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Interesting, are your relationships set up like the sample
    where Company->Employees is one to many? By this, I mean that assigning that employee to another company would still work out ok. (But you would not track employment history this way).

    Do you set up a new employee record for that person if they switch companies? That would give you problems that you're describing. If that's the case, I would consider a change in your table design.

    If you needed to track an employee's history you could add another table to accomplish that and still maintain one record per employee regardless of company. It'd be something like this:

    PKHistoryID (Auto)
    FKEmpID (To tblEmployee)
    FKCompanyID (ToCompany)
    EmpStartDate
    possibly other fields here.....

    I'm sure there's a solution for this but it's a bit difficult without seeing it.

    Lastly, you could still take the approach that you mentioned by counting shots per employee and storing. This would involve a bit of coding and I don't know if it's the best solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •