Building a new database

[GavryHagever]

Hi,

I'm building a new data base that his hole point is to have some details that can be sorted by a qustion, meaning no calculations.

I want to have a field with location but inside i want one record to have a few locations in the same field.
To have a few options that i can pick simultaneously in one field.

I want to be able to get reports from all the people from "India" for instance.

Hope I explained it in a way that you understood.

Many thanks to all that help me get the structure of what I'm trying to build!

 

[mike60smart]

Hi
You are describing MultiValueFields. These MVF's are recommended not to be used.

Can you describe your business process for the database?

 

[GavryHagever]

Hi
You are describing MultiValueFields. These MVF's are recommended not to be used.

Can you describe your business process for the database?

I attached an exel file example to make it more simple.
As you can see in the file, I have a lot of different options.
I want to be able to get a report of all the people that invest in AI from 1M$ plus for instence. I want to be able to sort like that, hope you get what I'm trying to say.

Thank you!

 

[Uncle Gizmo]

I want to be able to sort like that, hope you get what I'm trying to say.

How about this:- (Exports to Excel)

Show/Hide Columns AND Export to EXCEL - Nifty Access​

 

[mike60smart]

I attached an exel file example to make it more simple.
As you can see in the file, I have a lot of different options.
I want to be able to get a report of all the people that invest in AI from 1M$ plus for instence. I want to be able to sort like that, hope you get what I'm trying to say.

Thank you!

Hi
Can you upload a zipped copy of your current database?

 

[Pat Hartman]

I agree that multi-value fields are more trouble than help but in this case, it is even worse because you have ancillary data related to the InvestsIn field and that amount might be different for each value you stuff into the multi-value field so it would be far better in the long run to normalize the schema to begin with.

 

[Cronk]

Is it the location ("From") that you want multiple locations for a person (in the example nobody has 2 locations) or the investment area ("Invests In") where you do have multiple entries for the same person.

Either way, you hold the multiple values in a second, related table with the link being a PersonID. That way it is simple to select all people interested in a particular investment area.

 

[LanaR]

Why not break that single field into two fields, one to hold the minimum amount and the other to hold the maximum amount?

 

[GavryHagever]

Hi
Can you upload a zipped copy of your current database?

Hey I can't send the current database (it's classified), Its in excel anyways.
I trying to build a new access one that is built one the excel.

I want to have the options that i wrote in the last message i sent.
Thx!

 

[GavryHagever]

I agree that multi-value fields are more trouble than help but in this case, it is even worse because you have ancillary data related to the InvestsIn field and that amount might be different for each value you stuff into the multi-value field so it would be far better in the long run to normalize the schema to begin with.

Hi, Ok so Multi-value fields aren't good for me.
I don't understand what other way is an option.
thx!

 

[GavryHagever]

Why not break that single field into two fields, one to hold the minimum amount and the other to hold the maximum amount?

true, great tip thx.

 

[Gasman]

Hi, Ok so Multi-value fields aren't good for me.
I don't understand what other way is an option.
thx!

Reread the last paragraph of post #7

 

[mike60smart]

true, great tip thx.

You need tables as shown below:-

tblPerson
-PersonID - PK - Autonumber
-Firstname
-Surname
-CountryID - FK (Linked to PK from tblCountries)

tblCountries
-CountryID - PK - Autonumber
-Country (List of Countries UK, USA etc...)

tblPersonsInvestments
-PersonInvestmentID - PK - Autonumber
-PersonID - FK (Linked to PK from tblPerson)
-InvestmentTypeID - FK (Linked to PK from tblInvestmentTypes)

tblInvestmentTypes
-InvestmentTypeID - PK - Autonumber
-InvestmentType (List of types ie Retail, Medical, AI etc...)
-MinAmount - Currency (Do not use K,M,$ to indicate values)
- Currency (Do not use K,M,$ to indicate values)

See if you can create this and get back to us.

 

[Pat Hartman]

This is what we call a many-many relationship. It is implemented with a "third" table (tblPersonInvestments) which has TWO 1-m relationshipa. since it connects using PersonID to the person table and InvestmentID to the investments table.

Here's a sample database to show you how they work and the kinds of forms you would use. You'll have to "translate" my m-m to your m-m. The names are different but the underlying schemas are the same.

PS, just FYI, the multi-value group is implemented exactly this way except it doesn't allow you to have intersection data. That plus the fact that the relation table is hidden. I think is is even "deep" hidden so that it doesn't show when you click the options to show hidden/system tables.