Hi,

I don't know if this is possible.

I would like my report to list different records from a table that are not necessarily connected in any way.

For instance I would like to display up to four records at any one time but never more than four. The first could be maybe record 1, the second record 12, the third record 9 and the fourth record 26. This just an example the actual record numbers would be stored in a separate table.

Any ideas?

One way would be a multiselect listbox to select the desired records:

http://www.baldyweb.com/multiselect.htm

Hi Paul, Thanks for prompt reply.

I've only been using Access for a week or so, I'm just starting to get to grips with it.

Would it be possible to use this as a sub report?

I am building a Landlord's Certificates database.

At the moment I have five tables. The main table, Certs, brings all the data together to make one Certificate.

Each Certificate has a Client, Job address, and up to four appliances together with an inspection detail for each appliance.

I have managed to create a main form that is based on the Certs table that acquires and displays all the data for a Certificate.

I have also started to create a report to print out the Certificate. It is based on a query that selects one particular Certificate number. I have added the the Client and Job sections, that was quite easy as they are only one single record for each from different tables.
I'm now on the Appliances and Inspections and this is where I'm getting a bit stuck. I have started to do it with multiple sub reports but it's getting a bit tedious and messy.


----------
Neil

The technique I posted would not work for that, I don't think. What I would expect with a subreport is that the tables would be related, and I would be able to use the master/child links of the subreport control to keep the records in sync. Does your data support that (does the appliance data contain a field for certificate or client that would tie them together)?

Yes my data does support that.

In my case the Appl table hold the Appliances each with it's own ID (ID Appl)

and

each record in the Certs table has fields called ID Appl 1, ID Appl 2, ID Appl 3 and ID Appl 4

I've gone to my first sub report and used the master/child linker and linked

ID Appl 1 > ID Appl
ID Appl 2 > ID Appl
ID Appl 3 > ID Appl

I can't do ID Appl4 as it only let's me do three links.

However, when I run the report the subform is now blank and shows nothing.

i think paul is proposing a way of letting you get random details on a report

if however, you are trying to get RELATED records on a report, thats different matter

that is standard relational behaviour - you just need to have your tables constructed so that the main record, and the assocaited records have certain common fields - and then the report happens automatically without you having to do much work at all

is that what you mean?

I'm certainly not trying to get random records. Sorry if something gave that impression, though I'm not sure what it could be.

Neil, rather than 4 fields in the certs table, I'd recommend a junction table. It would include fields for certificate and appliance. If I had 3 appliances associated with my certificate, that table would contain 3 records. Then you have a very simple relationship with your subreport.

Paul's example worked perfectly. It lets you extract certain records from a table and display them in a simple report. I downloaded his sample db earlier and tinkered about with the code and could display a report from my Certificates form that showed all four records as I wanted, but, as he says, It doesn't work for a subreport.

Have a look at the attachment, It might give you better idea of what I'm trying to achieve.

Did you see post 7 relating to table structure? That would be the more normalized structure.

Paul, so I could have another table with only two fields say CertNum and AppNum. Then join CertNum to Cert ID in Certs and join AppNum with Appl ID in Appl.

How do I then get the subreport to pull the Appliance data into the report. Sorry if I seem a bit stupid.

--------
Neil

Your master/child link would be on CertNum. Then the subreport would display all records that had the same CertNum as displayed on the main report.

Any time you have fields like Whatever1, Whatever2, it's a sign of a normalization problem (repeating fields). In spreadsheets, you want "wide and short", but in databases, "narrow and long". In other words, records rather than fields. What if somebody decided you could have 5 appliances? You'd have to change the design of the table and all the objects that depend on it (forms, reports). A properly normalized design will handle it with no changes.

I take your point, normally that would be the case. I don't know if you've had a quick look at the Certificate PDF. As it shows, there can't be more than 4 appliances. However, as this project expands there will be a need to view all Certificates for a given Appliance so I can see that the new table will also help with that.

Time for me to go and make some changes !!!

I did look at the attachment, and in my experience, just because a form has space for 4 now doesn't mean someone won't get the bright idea to allow 5 at some point. My bosses do that type of thing to me all the time! :p

I think you'll be happier in the long run with that design, even if the number never changes.

I'm lucky, I'm the boss with the bright ideas as I'm my own boss, I designed the form as well.

Coming to think of it, it was my bright idea to get this computerised 'cos I was fed up with writing these out all the time.


Life's not all fun !!!

-------
Neil

paul/neil

obviously i misunderstood

neil originally said

I would like my report to list different records from a table that are not necessarily connected in any way.

paul mentioned a multi select list box

etc etc

i took it that the problem and solution was in respect of unrelated records, when there did seem to be a relationship of some sort.

How are you going to manage when you come across a customer with more than 4 appliances and why store the entire record when you have to issue the certificate manually at the time of the inspection anyway?:confused:

I've been writing certificates for 8 years and I don't think that I have ever needed to write out for more than 4 appliances. If we do, we'll do 2 Certificates, which will be really easy when this is finished.

If 'issue the certificate manually' means you have to physically write one out, you don't, printed is perfectly acceptable, the data can be captured electronically and printed later if required. However we'll be doing all on site at the time of our visit.

Paul,

I've started on adding the new tables. I going to add two. One for the Appliances and one for the Inspections.

The only problem I'm having is I'm using a back end database. How do I add the tables to my front end?


---------
Neil

You add them to the back end along with the rest, then link to them in the front end. In older versions, right-click in the Tables section of the database window and choose Link Tables. In 2007, it's on the External Data tab.

Thanks again

No problem, Neil. Post back if you get stuck.

Paul,

I'm having a problem getting my head around the new tables, maybe it's just me.

Dealing with only one table to start with, the new CertAppl. It has three fields, CertNum, ApplNum and LineID

As you previously mentioned it would be a good idea to have records corresponding to each line on the Certificate.

In my case I could have up to four records, all with the same CertNum but different ApplNum.

My problem is that I need to now integrate this with what I've already got but I just can't get my head around how to do it.

I thinking along the lines of updating the CertNum table when the Certificate is updated, but how? Is this the best thing to do?

Any suggestions would be appreciated.


----------
Neil

Not clear on exactly where you're stuck. I would present the data as a subform/subreport. For the transition, you'd run an append query for each of your 4 existing fields. For the first:

INSERT INTO CertAppl(CertNum, ApplNum and LineID)
SELECT CertNum, [ID Appl 1], 1
FROM ExistingTableName
WHERE [ID Appl 1] Is Not Null

I'm assuming that the code you have written is the append query and I'll need four of them for my application one to update each of the four Appliances.

I'm obviously missing something simple here because however I type in the code I'm getting compile errors.

----------
Neil

That would go into the SQL window of a query. Double check the table and field names, since I may have goofed them. Since this would be a one-time thing, I'd just run it, change the appropriate values for the second field, run it, etc.

I get it now that will load my new table up with the data.

I'd already done that.

I'm thinking along the lines of three scenarios

1. Create a new Certificate. Add the data to the table CertAppl

2. Amend a Certificate. Change the data already present in CertAppl

3. Delete a Certificate. (Should never need to be used) Remove data from CertAppl

This is where I'm stuck. How do I add, change and delete the required data in CertAppl. Or am I thinking this through totally wrong?

----------
Neil

1 & 2) You have a form for the certificate, a subform for the appliance, bound to that table. Master/child link on the certificate field makes sure the subform always reflects data associated with the certificate on the main form. The subform has a combo to select the appliance. That combo gets its selections from the appliance table (row source) but is bound to the field in CertAppl (control source). When you display a certificate in the main form, you'll be able to add/edit/delete records in the subform.

3) If you have referential integrity set up with cascade deletes, the data in CertAppl will automatically be deleted when the related certificate is deleted. You could also just code it to happen.

1 & 2) You have a form for the certificate, a subform for the appliance, bound to that table. Master/child link on the certificate field makes sure the subform always reflects data associated with the certificate on the main form. The subform has a combo to select the appliance. That combo gets its selections from the appliance table (row source) but is bound to the field in CertAppl (control source). When you display a certificate in the main form, you'll be able to add/edit/delete records in the subform.

3) If you have referential integrity set up with cascade deletes, the data in CertAppl will automatically be deleted when the related certificate is deleted. You could also just code it to happen.


Done all that now and well on my way to getting the Certificate to print out.

In your experience is it at all possible to have the combo's on my main form?

The combo to choose the appliance? I suppose, but you'd then have to code something to append the values into that table.

I've decided that due to a few problems with the inherent restrictions with sub reports on Main reports to create a table with all the fields required to print out a Certificate. When the Certificate is to be printed I'll use a button on the Certificates form to write all the data to the single record and then print out a report based on that data.

Would this do to put the data into the table?

DoCmd.RunSQL "INSERT INTO MyTable (Field1, Field2.....) VALUES (Value1, Value2.....)"

Or is there a better way?

If it is OK, Is there anything I should be aware of or look out for when constructing the SQL statement.

----------
Neil