Merging records for same ID with multiple items

[Aubs010]

I used thread700-958616 as a basis for a problem I had

This is a "made-up example" because the information I am working with is too complicated to explain and this simplifies it!

On a form, one of the fields in the form can have multiple answers.

I created the tables as follows:

tbl_Items[tt]
| ItemID | ItemName | ItemOwner |
|--------+----------+-----------|
| 1 | Ball | John |
| 2 | Pen | James |
| 3 | Pencil | Andrew |
| 4 | Ruler | Richard |
[/tt]
tbl_Colours[tt]
| ColourID | Colour |
|----------+--------|
| 1 | Green |
| 2 | Red |
| 3 | White |
| 4 | Blue |
| 5 | Yellow |
| 6 | Black |
[/tt]
tbl_ItemsColours[tt]
| ItemID | ColourID |
|--------+----------|
| 1 | 2 |
| 1 | 4 |
| 2 | 6 |
| 3 | 4 |
| 4 | 1 |
[/tt]

That's all fine, each item has a colour, however the ball has 2 colours.

When I look at the results in a query or form, I get:
[tt]
| ItemID | ItemName | ItemOwner | Colour |
|--------+----------+-----------+--------|
| 1 | Ball | John | Red |
| 1 | Ball | John | Blue |
| 2 | Pen | James | Black |
| 3 | Pencil | Andrew | Blue |
| 4 | Ruler | Richard | Green |
[/tt]

How can I represent this on a form or report as:
[tt]
ItemID: 1
Item Name: Ball
Item Owner: John
Colour: Red
Blue
[/tt]
or something similar?

Is it to do with the relationships or something? or do I need to create a query type with column headers?

Many thanks to anyone who helps

Much appreicated.

Aubs

 

[RoyVidar]

In a form, use a listbox or continuous/datasheet subform to display the repetaing infor (colours).

In a report, group by itemid, have the ItemID, ItemName and ItemOwner within that group header, and the colour in the detail section. An alternate way, creating a "denormalized view" of the colors (i e Blue, Green, ...), could be to use the sample function from dhookoms faq faq701-4233.

Roy-Vidar

 

[Aubs010]

Coincidence seeing you here Roy!!!

Thanks for the help in this forum and the last, I take it I've set it up as I should now? )

Still having a problem though...

In the form, I need to have a list of all the items, i.e. all the colours. but also have a list of the selected ones in a text box.

I had a ListBox populated from tbl_Colours and when a user selects an item, it ran some VB code to re-populate the TextBox.

This time, how can I get it to list all the items, but also save the items into the tbl_ItemsColours ?

Thanks again in advance


Aubs

 

[RoyVidar]

In a continuous form setup, I'd use the link/master child thingies to bind the itemID fields in tblItem and tblItemColour. Then for the colorID I'd considered using a combo looking up thlColours (a wizard generated combo would probably do).

Roy-Vidar

 

[Aubs010]

Hmmm, I'm still confused...

basically, I want it to look like this:
[tt]
/-------------------------------------------\
| ItemID: 1 |
| Item Name: Ball |
| Item Owner: John |
| Item Colour: Red/Blue |
| |
| Change Colour: |
| /--------------\ |
| | Green | |
| | Red | |
| | White | <--- ListBox |
| | Blue | |
| | Yellow | |
| | Black | |
| \--------------/ |
| |
| Record: (|<) (<) 1 (>) (>|) of 5 |
\-------------------------------------------/
[/tt]
(if you know what I mean)

and have it all on a single form, no sub forms.

This is because the form I am working on (with you!) is a sub form itself.

both sub form and main form are single forms, not continuous (has to be that way).


Does this explain what I am after better?

Aubs

 

[Aubs010]

The user selects 2 options and they populate the Item Colour (unbound) TextBox:

[tt]

/-------------------------------------------\
| ItemID: 1 |
| Item Name: Ball |
| Item Owner: John |
| Item Colour: Red/Blue |
| |
| Change Colour: |
| /--------------\ |
| | Green | |
| |[COLOR=red black] Red [/color]| |
| | White | |
| |[COLOR=blue black] Blue [/color]| |
| | Yellow | |
| | Black | |
| \--------------/ |
| |
| Record: (|<) (<) 1 (>) (>|) of 5 |
\-------------------------------------------/
[/tt]

Aubs

 

[Aubs010]

Sorry, another addition, hope you aren't being bombarded with emails!

The user selects 2 options and they populate the Item Colour (unbound) TextBox:

...but also populate the table tblItemColours

Aubs

 

[Aubs010]

Come on Roy, don't tell me you've gone home for the day?! I thought you were on a roll before!!!

Aubs

 

[RoyVidar]

Hey - this ain't my job, you know;-)

With such setup, I'd probably use something like dhookom's function as controlsource of the text control. So - I wouldn't allow the user access to the text control at all, only let them play with the listbox control. The text control would then only be a display of chosen colours.

For the listbox, I'd probably use tblColours as rowsource (or a query sorting them the way I'd like), including both the colourid and colour (first column, with a width of 0, probably), then use the doubleclick event of the listbox control to populate the tblItemColour.

For instance something like this (I'm using ADO):

[tt]dim rs as adodb.recordset
dim strsql as string
strsql="select itemid,colourid from tblItemColur " & _
"where itemid = " & me!txtItemId & _
" and colourid = " & me!lstList
set rs=currentproject.connection.execute(strsql,,adcmdtext)
if not rs.eof and not rs.bof then
' no record, add
strsql="insert into tblitemcolour (itemid,colourid) " & _
"values (" & me!txtItemId & "," & me!lstList & ")"
else
strsql="delete * from tblItemColur " & _
"where itemid = " & me!txtItemId & _
" and colourid = " & me!lstList
end if
rs.close
set rs=nothing
currentproject.connection.execute strsql
me.recalc[/tt]

- typed not tested - if you need to limit the number of colours, you could experiment with not using the selected item from the list in the criteria of the first recordset, test each record if it matches the listitem, and get a count of the records prior to deciding upon how/what to do.

Roy-Vidar

 

[Aubs010]

I know, I was only messing!!!

I can't look into it in depth right now, as I've now got a meeting tomorrow (great fun!!) that I need to prepare for! - looking forward to it!

Thanks for all your time though and I'll look into it on my return!



Aubs