If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I add dummy records to a subreport?
Hello,
I have a report for printing Security Incident Reports. On the first page there is a fixed three slots for up to three people. I need to have all three slots print even if there are less than three people in the incident. I have the data for people stored in a subtable. Any ideas are welcome. Thank you |
#2
|
|||
|
|||
How do I add dummy records to a subreport?
I would create a ranking column in the subreport's record source to number
the "people" records 1, 2, & 3. Then I would create a table of numbers with numbers 1, 2, & 3. Add this table to the subreport's record source with a join that includes all the records from the table of numbers. This will assure there are always 3 records in the subreport. -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Hello, I have a report for printing Security Incident Reports. On the first page there is a fixed three slots for up to three people. I need to have all three slots print even if there are less than three people in the incident. I have the data for people stored in a subtable. Any ideas are welcome. Thank you |
#3
|
|||
|
|||
How do I add dummy records to a subreport?
Duane,
I'm not sure what you mean. Could you elaborate a little more. This may help. The table fields look like this: SIRid, PersonNo, FirstName...... The incident report is alowed to have more than 3 people but the PersonNo 3 will be on another page at the end of the report. Thankyou for your responses "Duane Hookom" wrote: I would create a ranking column in the subreport's record source to number the "people" records 1, 2, & 3. Then I would create a table of numbers with numbers 1, 2, & 3. Add this table to the subreport's record source with a join that includes all the records from the table of numbers. This will assure there are always 3 records in the subreport. -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Hello, I have a report for printing Security Incident Reports. On the first page there is a fixed three slots for up to three people. I need to have all three slots print even if there are less than three people in the incident. I have the data for people stored in a subtable. Any ideas are welcome. Thank you |
#4
|
|||
|
|||
How do I add dummy records to a subreport?
There are a large number of posting regarding ranking queries that would
number your persons records from 1 to whatever for each SIRid which I expect is your foreign key to your main table. If you can't find info on a ranking query, come back with your table and signficant field names as well as data types. -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Duane, I'm not sure what you mean. Could you elaborate a little more. This may help. The table fields look like this: SIRid, PersonNo, FirstName...... The incident report is alowed to have more than 3 people but the PersonNo 3 will be on another page at the end of the report. Thankyou for your responses "Duane Hookom" wrote: I would create a ranking column in the subreport's record source to number the "people" records 1, 2, & 3. Then I would create a table of numbers with numbers 1, 2, & 3. Add this table to the subreport's record source with a join that includes all the records from the table of numbers. This will assure there are always 3 records in the subreport. -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Hello, I have a report for printing Security Incident Reports. On the first page there is a fixed three slots for up to three people. I need to have all three slots print even if there are less than three people in the incident. I have the data for people stored in a subtable. Any ideas are welcome. Thank you |
#5
|
|||
|
|||
How do I add dummy records to a subreport?
Duane,
I'm not sure what you mean by ranking query. the table look like this: SIRid - text (Foreign key) PersonNo - byte (I think this may be the ranking field) PersonType - Byte (1= Patient, 2= Visitor, 3= Staff, 4= Other) PCReportingParty - Yes/No |
#6
|
|||
|
|||
How do I add dummy records to a subreport?
What types of values are stored in PersonNo? Are these numbered 1,2,3,...? If
so you should be able to subtract 1 and integer divide by 3 to group your persons into groups of 3 GroupNum[PersonNo ]-1)\3 -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Duane, I'm not sure what you mean by ranking query. the table look like this: SIRid - text (Foreign key) PersonNo - byte (I think this may be the ranking field) PersonType - Byte (1= Patient, 2= Visitor, 3= Staff, 4= Other) PCReportingParty - Yes/No . . . PCSuspect - Yes/No FName - text (First Name) LNmae - Text (Last Name) RAddress - Text (Resadence Address) BAddress - Text (Bussness Address) Phone1 - Double Phone2 - Double Discription - Text The sub report's SQl is: SELECT * FROM tblSirPeople WHERE PersonNo 4 The Master-Child fields is SIRid. I need the 1st Page of the Master report to print 3 Person Blocks even if there are more or less people than three. In other words, if there are no people, for what ever reason, I need the report to print three person blocks. Currently I have the lines, boxes and labels on the master report with a subreport on top. I don't like this set up. It seems shaky to me. The SIRid and PersonNo fields do not print (They only exist for ranking and linking) thanks "Duane Hookom" wrote: There are a large number of posting regarding ranking queries that would number your persons records from 1 to whatever for each SIRid which I expect is your foreign key to your main table. If you can't find info on a ranking query, come back with your table and signficant field names as well as data types. -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Duane, I'm not sure what you mean. Could you elaborate a little more. This may help. The table fields look like this: SIRid, PersonNo, FirstName...... The incident report is alowed to have more than 3 people but the PersonNo 3 will be on another page at the end of the report. Thankyou for your responses "Duane Hookom" wrote: I would create a ranking column in the subreport's record source to number the "people" records 1, 2, & 3. Then I would create a table of numbers with numbers 1, 2, & 3. Add this table to the subreport's record source with a join that includes all the records from the table of numbers. This will assure there are always 3 records in the subreport. -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Hello, I have a report for printing Security Incident Reports. On the first page there is a fixed three slots for up to three people. I need to have all three slots print even if there are less than three people in the incident. I have the data for people stored in a subtable. Any ideas are welcome. Thank you |
#7
|
|||
|
|||
How do I add dummy records to a subreport?
Duane,
I don't think we are on the same page. Perhaps I have failed to express myself well. In the incident report, if there are less than 3 persons, I need to add blank records untill it reaches 3 records. So, if no people exist in an incident report, I need to add 3 blank or dummy records to the sub report. The fields SIRid and PersonNo can contain data in dummy records. Also I do not want to add any records to the actual table. "Duane Hookom" wrote: What types of values are stored in PersonNo? Are these numbered 1,2,3,...? If so you should be able to subtract 1 and integer divide by 3 to group your persons into groups of 3 GroupNum[PersonNo ]-1)\3 -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Duane, I'm not sure what you mean by ranking query. the table look like this: SIRid - text (Foreign key) PersonNo - byte (I think this may be the ranking field) PersonType - Byte (1= Patient, 2= Visitor, 3= Staff, 4= Other) PCReportingParty - Yes/No . . . PCSuspect - Yes/No FName - text (First Name) LNmae - Text (Last Name) RAddress - Text (Resadence Address) BAddress - Text (Bussness Address) Phone1 - Double Phone2 - Double Discription - Text The sub report's SQl is: SELECT * FROM tblSirPeople WHERE PersonNo 4 The Master-Child fields is SIRid. I need the 1st Page of the Master report to print 3 Person Blocks even if there are more or less people than three. In other words, if there are no people, for what ever reason, I need the report to print three person blocks. Currently I have the lines, boxes and labels on the master report with a subreport on top. I don't like this set up. It seems shaky to me. The SIRid and PersonNo fields do not print (They only exist for ranking and linking) thanks "Duane Hookom" wrote: There are a large number of posting regarding ranking queries that would number your persons records from 1 to whatever for each SIRid which I expect is your foreign key to your main table. If you can't find info on a ranking query, come back with your table and signficant field names as well as data types. -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Duane, I'm not sure what you mean. Could you elaborate a little more. This may help. The table fields look like this: SIRid, PersonNo, FirstName...... The incident report is alowed to have more than 3 people but the PersonNo 3 will be on another page at the end of the report. Thankyou for your responses "Duane Hookom" wrote: I would create a ranking column in the subreport's record source to number the "people" records 1, 2, & 3. Then I would create a table of numbers with numbers 1, 2, & 3. Add this table to the subreport's record source with a join that includes all the records from the table of numbers. This will assure there are always 3 records in the subreport. -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Hello, I have a report for printing Security Incident Reports. On the first page there is a fixed three slots for up to three people. I need to have all three slots print even if there are less than three people in the incident. I have the data for people stored in a subtable. Any ideas are welcome. Thank you |
#8
|
|||
|
|||
How do I add dummy records to a subreport?
I think I figured it out. In the subreport's open event, I perform a DCount
to see if 3 people exist and if they don't I add records to a temp table and join them with a union sql. "Nyx37" wrote: Duane, I don't think we are on the same page. Perhaps I have failed to express myself well. In the incident report, if there are less than 3 persons, I need to add blank records untill it reaches 3 records. So, if no people exist in an incident report, I need to add 3 blank or dummy records to the sub report. The fields SIRid and PersonNo can contain data in dummy records. Also I do not want to add any records to the actual table. "Duane Hookom" wrote: What types of values are stored in PersonNo? Are these numbered 1,2,3,...? If so you should be able to subtract 1 and integer divide by 3 to group your persons into groups of 3 GroupNum[PersonNo ]-1)\3 -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Duane, I'm not sure what you mean by ranking query. the table look like this: SIRid - text (Foreign key) PersonNo - byte (I think this may be the ranking field) PersonType - Byte (1= Patient, 2= Visitor, 3= Staff, 4= Other) PCReportingParty - Yes/No . . . PCSuspect - Yes/No FName - text (First Name) LNmae - Text (Last Name) RAddress - Text (Resadence Address) BAddress - Text (Bussness Address) Phone1 - Double Phone2 - Double Discription - Text The sub report's SQl is: SELECT * FROM tblSirPeople WHERE PersonNo 4 The Master-Child fields is SIRid. I need the 1st Page of the Master report to print 3 Person Blocks even if there are more or less people than three. In other words, if there are no people, for what ever reason, I need the report to print three person blocks. Currently I have the lines, boxes and labels on the master report with a subreport on top. I don't like this set up. It seems shaky to me. The SIRid and PersonNo fields do not print (They only exist for ranking and linking) thanks "Duane Hookom" wrote: There are a large number of posting regarding ranking queries that would number your persons records from 1 to whatever for each SIRid which I expect is your foreign key to your main table. If you can't find info on a ranking query, come back with your table and signficant field names as well as data types. -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Duane, I'm not sure what you mean. Could you elaborate a little more. This may help. The table fields look like this: SIRid, PersonNo, FirstName...... The incident report is alowed to have more than 3 people but the PersonNo 3 will be on another page at the end of the report. Thankyou for your responses "Duane Hookom" wrote: I would create a ranking column in the subreport's record source to number the "people" records 1, 2, & 3. Then I would create a table of numbers with numbers 1, 2, & 3. Add this table to the subreport's record source with a join that includes all the records from the table of numbers. This will assure there are always 3 records in the subreport. -- Duane Hookom Microsoft Access MVP "Nyx37" wrote: Hello, I have a report for printing Security Incident Reports. On the first page there is a fixed three slots for up to three people. I need to have all three slots print even if there are less than three people in the incident. I have the data for people stored in a subtable. Any ideas are welcome. Thank you |
Thread Tools | |
Display Modes | |
|
|