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??????
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.
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 )
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"
Doe, John 123-45-6789 12-12-12 11-12-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.
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.
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......
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.
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: