Non consecutive records in report.

[neilhoop]

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?

 

[pbaldy]

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

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

 

[neilhoop]

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

 

[pbaldy]

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)?

 

[neilhoop]

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.

 

[gemma-the-husky]

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?

 

[pbaldy]

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.

 

[neilhoop]

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.

 

[pbaldy]

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

 

[neilhoop]

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

 

[pbaldy]

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.

 

[neilhoop]

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 !!!

 

[pbaldy]

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!

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

 

[neilhoop]

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

 

[gemma-the-husky]

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.

 

[Rich]

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?

 

[neilhoop]

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.

 

[neilhoop]

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

 

[pbaldy]

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.

 

[neilhoop]

Thanks again