I'm trying to use the SHAPE command create a hierarchical recordset for a report in VB6. I'm using the Data Report Designer in VB and an Access database. This is a report of all client activity for a specified date range. I have a parent Client table with two child tables, FoodService and PaymentHistory.
Client table: ClientID, LastName, FirstName, Address, SocialSecurityNumber
-----FoodService Table: FoodServiceID, ClientID, FoodServiceDate, FoodServiceType, NumberOfBags, Comments
-----PaymentHistory table: PaymentID, ClientID, PaymentDate, PaymentType, AmountReceived, AmountRequested, Comments
Somehow, I want to combine the FoodService and PaymentHistory records for the specified date range into one child record under the parent Client record that would look something like this:
Parent Client: ClientID, LastName, FirstName, Address, SocialSecurityNumber
---- Child: ServiceDate, ServiceType ("Food" or "Payment"), ServiceDescription, Amount1 (number of bags), Amount2 (amount requested), Amount3 (amount received),ServiceComments
Since the two child tables are a little different from each other, I don't know how to combine them into one record that I can then use to create one line item for the report.
Is there a way to do this with the SHAPE command?