I have been stuck on this for a while, and just can't get past it. I have one table, "sales", with auto_increment primary key sale_id. This table has a many-to-many relationship to the "services" table, with auto_increment primary key serv_code. These two tables are linked through the services_sales table, which has an auto_increment primary key, and the sale_id and serv_code as foriegn keys. So I can look up all services linked to an individual sale, or all sales linked to a certain service.
I have a form where you specify sales person, month, and year, and click "Generate Report" to generate the report. The report correctly pulls up each sales record for specified sales person and specified month and year. But each of these sales records has a one-to-many relationship with the services sold. I can't figure out how to display the services for each sale on the same line. I have a query that can look that up from the services table if you give it the sale_id, but the report is bound to the query that pulls information from the sales table.
So the report gives a list of each sale. Each line has sale date, business name, customer type, etc., all coming from the sales table. But for example, one sale record may be linked to three services (the number of services per sale varies). Ideally I would pass the sale_id onto a second query that would look up the services connected to that sales record, and via code concatenate them all into a string so they could be included on the report. So each line would have sale date, business name, customer type, etc., and services would just be another column on each line.
I just can't figure this out. Please let me know if you have any ideas or if the above isn't clear. Thanks for your help.
I have a form where you specify sales person, month, and year, and click "Generate Report" to generate the report. The report correctly pulls up each sales record for specified sales person and specified month and year. But each of these sales records has a one-to-many relationship with the services sold. I can't figure out how to display the services for each sale on the same line. I have a query that can look that up from the services table if you give it the sale_id, but the report is bound to the query that pulls information from the sales table.
So the report gives a list of each sale. Each line has sale date, business name, customer type, etc., all coming from the sales table. But for example, one sale record may be linked to three services (the number of services per sale varies). Ideally I would pass the sale_id onto a second query that would look up the services connected to that sales record, and via code concatenate them all into a string so they could be included on the report. So each line would have sale date, business name, customer type, etc., and services would just be another column on each line.
I just can't figure this out. Please let me know if you have any ideas or if the above isn't clear. Thanks for your help.
