New to Queries - need multiple results from one entry

[totalnovice2]

Hi.

I have looked and can't seem to find anything which helps so I have started a new thread.

The below isn't the actual use but it is easier to explain like this...

I have a system set up on access and I have a front page (form) where I can enter the reference number (1141#2) of a building (YELDR for example).
This then fills in information in the other fields on the front page. For example address, number of doors and entry codes.

My problem is that this only displays one door and one entry code. I need it to display all doors and all entry codes so that I can just type in my reference number and have something that looks like the below but for all of them. It works fine at the moment but only for one of the results when there can be up to 10.

Reference No. YEDJR <---------------- I ENTER THIS

Address - high Street - London - UK <-------------- All of these fields are populated using the query

Doors - Green - front <-------------- All of these fields are populated using the query
- Red - side <-------------- All of these fields are populated using the query
- Orange - back <-------------- All of these fields are populated using the query

Codes - 10111 <-------------- All of these fields are populated using the query
- 10221 <-------------- All of these fields are populated using the query
- 10256 <-------------- All of these fields are populated using the query

I know this is probably nowhere near enough information but is this possible? I don't know SQL so i would have to use the designer.

This is the SQL which I have at the moment though (I didn't write it i just copied this from design view on the query)

SELECT [Site Info Data].[1141 #2], [Site Info Data].[1141 #2], [Site Info Data].[Site 2 Name], [Site Info Data].freq, [Site Info Data].status, [Site Info Data].[Site 2 Owner], [Site Info Data].[NGR #2], [Site Info Data].[Address #2], [Site Info Data].[Postcode S2], [Site Info Data].[Dish size #2], [Site Info Data].[Dish height #2], [Site Info Data].[Dish Bearing #2], [Site Info Data].RCLO
FROM [Site Info Data]
WHERE ((([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141] And ([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141] And ([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141]));

 

[namliam]

1) try changing your form from "Single view" to Continuous View
2) Dont use # and spaces in column names inside your database, it makes for a mess and can cause problems/unexpected things if you are not carefull.
4) Atleast somewhat format code if you post it on forums...

WHERE ((([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141] 
    And ([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141] 
    And ([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141]));

a) why are you doing 3 likes that are doing the exact same?
b) Does the user enter *something* into thei text box? I would expect not... if not that makes the "like" work the same as =, is your intention to be able to search for part of a string or do you want to search only for specific sites?
if you want to use like you probably should make something like...

WHERE ((([Site Info Data].[1141 #2]) Like "*" & [Forms]![Front Page]![txt1141]  & "*"

 

[totalnovice2]

Thanks for the reply.

Yes the 1141#2 field is where a user would enter text and then the rest of the fields are populated.

On the attached the first picture is the front page. The box on the left highlighted in red is where the site reference is entered and the larger box to the right is all populated using this.

It sources the information from the table on the second picture. I only need the address and postcode once but the other fields, such as dish height, dish size and dish bearing could have up to 10 results on there but it currently only populates one result.

 

[namliam]

1) did you try 1 from my previous post?
2) You have some design issues here, data that is repeating is (99% of all times) a design flaw.
3) Your screen sample and table sample dont match up, its confusing IMHO... but it looks like you are looking for an exact match only which you do with = not with "like" and only 1 time not 3 times

 

[totalnovice2]

I am in the process of working out how to do 1) from your first reply. I am a total novice so it takes me a while to figure these things out.

I'm OK with VBA coding but other than that I am rubbish.

The data which is repeated is actually because there are different dish sizes and heights further along which have the same site reference number (1141#2). This is what I need to capture - all information from all lines on the table which have the same 1141#2.

OK thank you for your help so far.

Hopefully this will help me get what I need.

 

[namliam]

I am sure 1141 makes sence, but in general having a column that is all numbers is Meh...
Also the #2 makes me suspect there is a #1 as well?

The design flaw is actually in the sence there there should be one table/record (tblSite) that holds
1141 #2 - Site 2 Name - C/S Reference - NGR - etc....
all the repeatative information that determines the site itself.

All the dishes should be in a seperate table/record (tblDishPlacement) that only holds the dish information
1141? - Dish size - Dish Height - Dish Bearing - etc.
Only common information in both tables should be a key value (1141?) or possibly a database key or some other field that links the two.

Arguably there should be a "simple" table (tblDishType) as well. which stores the different types of dishes that you can place... which leaves only the bearing as a unique information to the (tblDishPlacement)
Dishtype would contain things like size, height, weight, amplitude, colour, etc... spicific to the different types of dishes.