List Bound Column dilemma 1

[ZOR]

I have a standalone table containing 10 rows, 2 fields, ID(Autonumber), and an item. The item is a word. The table is the rowsource of a combo on a form. I thought to save storage space the ID would get stored in the underlying table of a form, but the user would see the word to choose. (ie bound combo column is the ID)

Now I've come unstuck, as when I run a query I get four fields (there are four fields that use the combo selection) showing ID numbers. Any ideas how I sort the problem out?. THe query first fills a listbox for the user to see, and then gets used as a report source.

List rowsource is:

SELECT DISTINCT TXCLIPS.NName AS Name, TXCLIPS.Start AS [Timecode In], TXCLIPS.Duration, TXCLIPS.StarRating, TXCLIPS.Comments, TXMASTERS.Barcode, TXMASTERS.SeriesName, TXMASTERS.EpisodeTitle, TXCLIPS.NName, TXCLIPS.Start, TXMASTERS.Typeofmaterial, TXMASTERS.TapeStandard, TXMASTERS.Audio1, TXMASTERS.Audio2, TXMASTERS.Audio3, TXMASTERS.Audio4, TXMASTERS.Subtitles, TXMASTERS.Stageofcompetition, TXMASTERS.EventDate, TXMASTERS.Venue, TXMASTERS.AdditionalInformation, TXMASTERS.Competition
FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1
WHERE (((TXMASTERS.Barcode)=[FORMS]![TapeLogs].[BCODE].[CAPTION]))
ORDER BY TXCLIPS.Start;

Its the Audio1,Audio2,Audio3,Audio4 fields giving me the problem.

The Audio Table is:
Audio ID (Autonumber)
AudioType Text

Wish now I had stored words. Thanks

 

[dhookom]

You have a normalization issue. If you didn't have four Audio fields, you could just join in a single copy of the Audio table. Since you have repeating fields in your table, you need to join with four copies of the Audio table.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

Many thanks. Next time I will go for putting in the word/s selected. All works, thanks again.

 

[dhookom]

Does this suggest you will retain four audio fields? IMHO, this would be a bad idea.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

Thanks. The problem is the table has four fields among many. The four fields have four combos where the user selects appropriate data. A table holds the rowsource data for the combos. I could have four tables for the four combos, but that makes it a bigger operation if the user wants to add data into the table, ie add more technical words. as whatevers in the table has to be common for any combo to select. So I am stuck knowing what else to do. Regards

 

[dhookom]

IMHO, you should remove all the Audio fields from TXMASTERS. Then create a related/junction table:
[tt][blue]
TXMasterAudio
==================
MasterAudioID
ID1 links to TXMASTERS.ID1
AudioID links to [Audio Table].[Audo ID]
[/blue][/tt]
This will allow you to specify any number of related audio records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]