What report would suit best to display information?

[Cyberice]

Hi all,

I have a little perplexing problem. I have a big database with three tables in. I have built a simple query so that when you enter a post code it so the results of address concatenated also depending on the number in extra collection information {yes} or {no} that too gets displayed.
I can produce a report no problem but I was wondering from the query could I produce just a list of the address concatenated i.e. from the postcode {WF8 2AR} then select one line i.e number 7 then show the address plus Field 2 {and or} Field 3 located in table collection day information and table extra collection information, then a sample return to the post code form.
I am on YouTube at mo, so trying to build the form or report myself. I hope this make some kind of sense.
Thanks in advice,

Post Code ID WF8 2AR WF7 7PE

Mick.

[rpeare]

First of all your Optimized_Calendar_Pattern in your table Main Dataset should be storing the ID from Collection Day Information, NOT the text value in Field1
Similarly your field Extra Collection Information should be storing the ID from the table Extra Collection Information, NOT the text value of field 1

You will encounter no end of problems if you continue with this design as is.

Secondly, you are storing a calculated value (at least one) in 'Address_Concantenated' in your table Main Dataset, do not store calculated values, they can be figured any time you run a query or report and just waste space in your table.

Third, do not use spaces or other special characters in any of your database object names (table names, field names, controls, forms, reports, etc) they will cause you problems as you progress with MS access.

Lastly, you can concantenate the fields any way you want in a query so for instance if you wanted the Address_1, Street_Name then Field1 from CollectionDayInformation (if it exists) then field1 from ExtraCollectionInformation if it exists with a formula like

NewConcant: [Address_1] & " " & [Street_Name] & " " & iif(isnull([Collection Day Information]![field1]), "", [Collection Day Information]![field1]) & " " & iif(isnull([Extra Collection Information]![field1]), "", [Extra Collection Information]![field1])

[Cyberice]

Hi there,

Thanks for the advice but I am only a novice here.... Would I be able to use the code to place it in a form? This is what I need to build. I don't mind doing it and trying to learn how to do it, but I need a starting point.

Again thanks,

Mick.

[rpeare]

You can place it wherever you want, where you calculate it is up to you, if it's in a query driving the form that's probably the easiest thing, but you can just as easily have it as a formula on the form itself in the CONTROL SOURCE property.