I hope someone can help. I am evaluating some software; the front end is VB/Microsoft Forms & the back end is Access. As such, I only have access to custom queries. The question is whether I can construct a query which can be used, either within the program or by exporting to a CSV file and then using mail merge, to automatically generate custom multipage ‘reports’.
The report involves data from a number of tables, some of which will (or more accurately, *may*) return multiple results ). That’s where the issue lies; using joins, I have written a query which returns the necessary information; my problem is that where there is more than one relevant result in one of the tables, I get more than one record. What I want to do is amalgamate the data so that I have one record with all the necessary information, as the report itself allows for up to 4 items. So although I can obviously run the query and have it save it, and then manipulate the CSV file afterwards, that in itself is time consuming. I just can’t think if there is a way, within one query, to get all the information into one line – and obviously I need to alias any multiple items as item.item1, item.item2, etc.
There are time constraints which determine whether to go with this software or continue writing the comparative system I started quite some time ago. The system is pretty complex and client work means I don’t really have the time to work on it but if this system can’t be made to produce the reports I need automatically (I’m currently generating them by hand which is very time consuming) then it would be more sensible to carry on with my own system. I’ve been going round in circles with the query, so I’d appreciate some input and I’ll drop the idea, if it can’t be done, rather than wasting any more time on it.
The query so far –
SELECT DISTINCT customer.surname AS surname, customer.forename AS forename, customer.addr1 AS address1, customer.addr2 AS address2, customer.addr3 AS address3, customer.postcode AS postcode, customer.home_tel AS home_tel, customer.work_tel AS work_tel, customer.email AS email, customer.mobile AS mobile, pet.pet_name AS pet1, breed.breed_desc AS pet1_desc, pet.petcolour AS pet1_colour, pet.dob AS pet1_DOB, pet.sex AS pet1_sex, pet.neutered AS pet1_neuter, pet.isolate AS pet1_share, pet.pet_notes AS pet1_notes, pet.feedtype AS pet1_food, pet.feed_notes AS pet1_foodnotes, pet.medic_notes AS pet1_medicine, pet.microchip AS pet1_chip, vet.practice_name AS pet1_vet, vaccination.vacc_date AS pet1_vaccdate, vaccination.vacc_desc AS pet1_vacc, booking.start_date AS startdate, booking.end_date AS enddate, booking.start_time AS starttime, booking.end_time AS endtime, booking.gross_amt AS total, booking.paid_amt AS deposit, booking.amt_outstanding AS outstanding, booking.bk_no AS booking
FROM breed INNER JOIN ((vet INNER JOIN ((customer INNER JOIN booking ON customer.cust_no = booking.cust_no) INNER JOIN pet ON customer.cust_no = pet.cust_no) ON vet.vet_no = pet.vet_no) INNER JOIN vaccination ON pet.pet_no = vaccination.pet_no) ON breed.breed_no = pet.breed_no
WHERE (((booking.bk_no)=54)) OR (((booking.bk_no)=76));
As it stands if the customer has, for instance, 3 pets then there will be three records and the only way to get it into the form is to add the fields pet2_xx, pet3_xx, etc, to the CSV file, move the relevant data into those fields and delete the extra records.
Thanks in advance for your input!
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar