Storing Records 1

[lars7]

Hi Guys,

Need help with this one.

I have been asked to provide a way to record and then show in a report what media sources were used to advertise a vacant post but they have decided that they would now like to record more than 1 per vacancy. Does anyone know the best way to store this information and still be able to report on it easily?

The “Ad Id” (Numeric) is stored in table “Vac Board” in a field called “Media”.

Current Data Sample:

Vac Board
Media Job Ref No
1 685
2 686
2 687
1 688
3 689
3 690
4 691
2 692
1 693
1 694


This information is taken from table “Ads”

Data Sample:

Ads
Ad ID Ad Name
1 Herald/Sunday Herald
2 Daily Record/Sunday Mail
3 Metro
4 Word of Mouth

 

[lespaul]

The table VacBoard - does it only consist of those two fields? What's the PK? As long as you don't have a unique index on the Media & Job Ref No, you should be able to just add the new records to your existing table.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[lars7]

Hi Lespaul,

Sorry I think I need to explain this better:

Job Ref No is the primary key for the vacancy. So I think I am looking for something like this:

Media Job Ref No
1,2,3 685
2 686
2,3 687
1 688

Can this be done and if so how would I display in a Query the media types used for that vacancy?

Or is there a better way.

As I don't know how many media type could be entered I am trying not to add more fields ie, media2 media3 media4 ect,

Hope this is more clear.

 

[lespaul]

ok you'll need to change the PK to a composite PK that consists of both Media and Job Ref No and you'll store the records like this:
[tt]
Media Job Ref No
1 685
2 685
3 685
2 686
2 687
3 687[/tt]

To change the key, go to the table design, highlight both fields and press the key button.

HTH
Leslie

 

[lars7]

hi Lespaul,

I can see that is the best way to go with now thanks.

 

[lespaul]

thanks for the star!

If it turns out you need to report:

[tt]
Media Job Ref No
1, 2, 3 685
[/tt]

there's a FAQ that has a module that concatenates the one to many relationship into a single output like I've showed above.