One Field to Combine Multiple Values from that Field into One

[grubnz]

Hi there,

Hope all is well.

I am building a database at work.

I was going to use the multiple field in access but then that didn't fit the design principals and also reading some other posts apparently it isn't a good idea to use this functio anyhow.

Maybe I am not doing this correctly. So any advice is greatly appreciated.

I am wanting is to combine the many field for that record into one field so that when we need to copy the Aircraft Number into SharePoint we don't have to select each one this could be quite time consuming.

tblPubDetails
PubPk, CompanyFk, AircraftHoldingFk
1, 2, 1
2, 2, 2
3, 2, 3
4, 1, 1
5, 1, 2
6, 1, 3
7, 3, 3

tblAircraftHolding
AircraftHoldingPk, PubFk, AircraftFk
1, 1, 1
2, 2, 2
3, 3, 3
4, 4, 1
5, 5, 2
6, 6, 3
7, 7, 3

tblRefAircraftDetails
AircraftPk, AircraftType
1, 50
2, 100
3, 320

Once it has been entered into individually I would then like a field to show the AircraftHoldingFk all together in the one field e.g.Aircraft on the form. Not sure if the above makes sense Sorry if it doesn't.

PubPk, CompanyFk, AircraftHoldingFk, Aircraft
1, 2, 1, |1, 2, 3 (50, 100, 320)
2, 2, 2, |
3, 2, 3, |
4, 1, 1, |1, 2, 3 (50, 100, 320)
5, 1, 2, |
6, 1, 3, |
7, 3, 3, |3 (320)

Maybe this can't be done or if there is a better way to approach this any assistance is greatly appreciated. :banghead:

Thank you

Have a wonderful day.

All the best,

Annie

 

[Minty]

Have a look at allen browne Concatenate function. http://allenbrowne.com/func-concat.html

 

[MarkK]

I don't get the purpose of the database. What's a Pub? What's an AircraftHolding? Why do PubDetails and AircraftHolding tables seem to have a circular reference?

 

[grubnz]

Hi Minty and Mark,

Minty - Thank you for the link I will have a look at this.

Mark - Pub - Publication, AircraftHolding where I held the aircraft types for a particular record.
e.g. I have 1 publication but this publication maybe used for three different aircraft types. So it would be 1 to N. The reference table would be the aircraft types so that AircraftHolding table would only hold a number not the complete name.

So instead of having
1 1 or 50
1 2 or 100
1 3 or 320

It will show on the form in one field.
1 (1,2,3) or (50, 100, 320)

I hope that makes sense. If I have made a circular reference. Is there a better way to do this?

Thanking you both for taking the time to assist me with this issue it is greatly appreciated.

Have a lovely day.

All the best,

Annie

 

[MarkK]

I still don't understand the purpose of the database.

I have 1 publication but this publication maybe used for three different aircraft types.

How does the "use" of a publication in respect to aircraft become a candidate for a database system? And not knowing that, it's impossible to answer your question....

Is there a better way to do this?

...a better way to do what?

Hope this helps,

 

[grubnz]

Hi Mark,

Thank you for your response.

I work for an airline. Publications to maintain aircraft with, are link to an aircraft type or multiple types depending if it is a generic compnent.

Hope I have clarfied it a bit better.

Thank you

Have a wonderful day.

Annie

 

[MarkK]

In that case I would expect to see an Aircraft table and a Publication table, and then a third table, maybe called AircraftPublication, that joins the two.

tAircraft
'describes the aircraft
AircraftID (PK)
Manufacturer
Model

tPublication
'describes the publication
PublicationID (PK)
Company

tAircraftPublication
'describes the relationship between an aircraft and a publication
AircraftPublicationID (PK)
AircraftID (FK)
PublicationID (FK)
EffectiveDate

And then if you make a subform using the tAircraftPublication table, you can use that subform on both the Aircraft and Publication forms. Each aircraft can have a list of publications, and each publication can have a list of aircraft, depending which foreign key you use to join to the parent form.
Does that make sense?