Solved What is the best decision for cross-reference data? (1 Viewer)

[alvesi]

Hello everyone,

I have a challenge to build, manipulate and display cross-reference data. I may not use the right terminologies as I am not proficient in Access, but I'll try to explain myself. Any doubts just ask me to clarify.

Essentially, I have multiple sources of data. I need to organize them in multiple cross-reference possibilities. Then to create multiple usage scenarios aggregating other sources of data at this final stage. And I need to be able to create reports at any step of the process.

Scenario 1: The look up on a table. It is useful as I can easily recognize information by its "name text". But it only works at the first time I cross reference the data. After the source data has been combined on a second table. I then need to select this data combination - via look up again - but now in a third table. But this time I can only see the ID Number and no longer the "name text". How can I resolve that? Or workaround that?

Scenario 2: The combo box in forms. But the same occurs right from the first atempt to combine the data. I can only see the "ID Number" and not the "Name Text". How can I resolve that? Or workaround that?

On both scenarios I end-up having the "name text" replaced by the "ID Number". Nothing against, I understand the importance. But as a matter of fact, while the "ID Number" is very important, it becomes a nightmare to work as above described. I do not want to get rid of the ID but I do need to be able to read the "name text".

Below I send a small display of what I am trying to achieve.

Source: Provide the data
Cross-Reference: Creates multiple data combinations also adding extra data on top.
Scenario Builder: Creates multiple scenarios vs multiple data combinations adding extra data on top from itself and from the "Resources".

As you see, "Data 1a" from "Source" gets combined in the "Cross-Reference" and is manipulated in the "Scenario Builder" which is another data combination.

Scenario 1 - look up table: I have the "name text" up to "Cross-Reference" but it turns into "ID Number" in "Scenario Builder".

Scenario 2 - combo box: I get "ID Number" in "Cross-Reference".

How can I resolve that? Or workaround that?

Thank you in advance for your support.

Regards, Isac

 

[jdraw]

Can you provide a more concrete example with some sample data to help communicate the subject matter involved and the cross references(s) required?

Always a good starting point to mock up a scenario and try it with some test data. Get others (different user perspectives) involved in the process/review to ensure "all requirements are addressed".

 

[theDBguy]

Hi. Welcome to AWF!

The real power of a database is in its querying abilities.

It is not recommended to use lookup fields, so you might start there and remove them from your tables, so that your tables only show the id values. Then, when you're ready to see the migration patterns with the text values, you will use queries to connect the dots and display the results.

 

[alvesi]

Hi. Welcome to AWF!

The real power of a database is in its querying abilities.

It is not recommended to use lookup fields, so you might start there and remove them from your tables, so that your tables only show the id values. Then, when you're ready to see the migration patterns with the text values, you will use queries to connect the dots and display the results.

Hi theDBguy,

Thank you!

I am happy to do so. My only problem is how to implement it.

My first question would be, using query will I be able to see the "name text" and not be forced to see the "id number"?

The second question would be, that immigration patterns are far from being an exact science, therefore requiring tons of hands-on fine-tuning to accommodate data in a certain fashion. Many people after immigrating nationalized their homeland names and family names, even changed them entirely or changed their original nationality for the new one acquired. And the more you go back in time the worst it gets. So the changing options would never end. With that in mind, I guess the query will be a rather static view of a certain data combination. How do I gain flexibility with that queried data? Can I update or manipulate bits and pieces of it? In the end, it is no longer a table where I can add, delete, or update data. Is rather a static view combining data. Right?

Thank you for your help.

Cheers, Isac

 

[alvesi]

Can you provide a more concrete example with some sample data to help communicate the subject matter involved and the cross references(s) required?

Always a good starting point to mock up a scenario and try it with some test data. Get others (different user perspectives) involved in the process/review to ensure "all requirements are addressed".

Hi Jdraw,

sure, but as I am still developing the rational and testing it, I don have yet real data employed. But I can tell you how it will be used.

Purpose: This is a data-base to support writing a thesis on immigration patterns.

Source: Names, Family Names, Locations

Cross-Reference: Create multiple combinations of the data source that will profile a certain individual.

Scenario Builder: Through the profiled individual, map him against his “immigration profile” and add “immigration indexes” from “Resources” to create multiple deviations scearios that can be used for analysis.

I did put in that innitial manner as applying the concept can be rather confusing as it would require more in-depth SME discussion.

But valid to say that the profiled individual is represented by the “Data 1a” in my graphic.

Hope it helps to link the dots. But hoping to not sin by being to simplistic, independent of the data and the mechanics that I will use, the matter for me is how can I visualize the “name text” instead of the “id number”?

And thank you for your prompt reply.

 

[mike60smart]

It always helps if you use actual field names and then to give actual data for each table rather than
using Data 1a, Data 1b etc

 

[The_Doc_Man]

The diagram you showed us is not a traditional cross-reference. It SEEMS (and this is only an impression) that you might be dealing with what we call "cascading combo boxes" where a choice from box A will affect what you can choose from box B, which may go on to affect box C, and so on.

A "cross-reference" in most uses is a list of places in large document A where item, name, or topic B is referenced. Your diagram does not show this.

Your post #5 suggests that you don't actually have a cross-reference (though in fact you might), you have a hierarchy akin to genealogical research. A cross-reference here would be possible. I have done something like this. See, for example,

Best way to create a directory: Word mail merge vs Access reports?

Hello everyone, I need to create some directories off several ERP extractions. As far as I can remember, I can use the mail merging system of Word (using labels), but an Access report would be very easy to generate as well. What I am not sure about is wether I can insert some titles or subtitles...

www.access-programmers.co.uk

 

[alvesi]

It always helps if you use actual field names and then to give actual data for each table rather than
using Data 1a, Data 1b etc

Hey Mike60Smart,

You are right. And "Data 1a" is compatible with the person's name and will go up on the chain. It is the main reference data. All the others are additions here and there.

Hope it clarifies.

Cheers
Iac

 

[LarryE]

Hi Jdraw,

sure, but as I am still developing the rational and testing it, I don have yet real data employed. But I can tell you how it will be used.

Purpose: This is a data-base to support writing a thesis on immigration patterns.

Source: Names, Family Names, Locations

Cross-Reference: Create multiple combinations of the data source that will profile a certain individual.

Scenario Builder: Through the profiled individual, map him against his “immigration profile” and add “immigration indexes” from “Resources” to create multiple deviations scearios that can be used for analysis.

I did put in that innitial manner as applying the concept can be rather confusing as it would require more in-depth SME discussion.

But valid to say that the profiled individual is represented by the “Data 1a” in my graphic.

Hope it helps to link the dots. But hoping to not sin by being to simplistic, independent of the data and the mechanics that I will use, the matter for me is how can I visualize the “name text” instead of the “id number”?

And thank you for your prompt reply.

What do you mean by "Locations". If you are going to track where people immigrate from, then they could come from many various continents, which contain countries, which contain regions, which contain cities, whicn contain neighborhoods. So how detailed do you want to get?

 

[jdraw]

Isac,

Don't be too quick to jump into physical database!! Most database related problems are traced back to design issues.
data 1a and data 1b may be great for you and those intimately familiar with the data, but it's too abstract to offer meaningful advice.

You didn't mention immigration patterns in your initial post. So, based on my more general comment in post#2, and on LarryE's comment, I suggest you step back --forget Access, combo boxes etc- for the moment. What exactly are the Subjects for analyzing "immigration patterns". To immigrate you must emigrate ( to A from B), and what level of detail (LarryE gave an example of "scope"). Is there an ethnicity, religious or other theme to your investigation? Is it immigration to Country X or immigration to various Countries/Continents/...

Just to see if we're on the same "wavelength", I posed this question to ChatGPT:

What subject matter(entities and attributes) would you expect in an analysis of immigration patterns?

Here is the response that I hope helps you with design.

An analysis of immigration patterns could involve various subject matters and entities. Here are some potential entities and attributes that could be considered in such an analysis:

1. Countries of origin: This would involve examining the countries from which immigrants are coming and identifying the number of immigrants from each country.
2. Countries of destination: This would involve examining the countries where immigrants are going and identifying the number of immigrants going to each country.
3. Demographic information: This would involve looking at the age, gender, and other demographic characteristics of immigrants, as well as the reasons for emigrating.
4. Education and skill level: This would involve examining the educational background and skill level of immigrants and assessing the impact of these factors on the immigration patterns.
5. Economic factors: This would involve looking at the economic conditions in both the countries of origin and destination and examining the impact of these factors on the immigration patterns.
6. Immigration policies: This would involve looking at the immigration policies of the countries of destination and assessing the impact of these policies on the immigration patterns.
7. Legal status: This would involve examining the legal status of immigrants in the countries of destination and identifying any patterns or trends in the legal status of immigrants.
8. Social integration: This would involve examining the social integration of immigrants in the countries of destination and identifying any challenges or barriers to integration.

Overall, an analysis of immigration patterns would involve examining a range of entities and attributes related to both the countries of origin and destination, as well as the individual immigrants themselves.

 

[LarryE]

If you are still interested in suggestions for your database design, here is what I might begin with just based upon what information you provided. The table, field and relationship structure looks like this:

Assuming you wish to track where prople came from and where they immigrated to, this structure assumes:

1. Each continent contains multiple countries
2. Each country contains multiple Regions and/or States
3. Each Region/State contains multiple Local Regions and/or Cities
4. Each Local Region or City contains multiple family names
5. Each family name contains multiple Family Individuals who immigrated to a specific continent, country, region or state and local region or city on a certain date. They also have a status (legal or illegal) and Date Of Birth (DOB) so that their age at immigration can be calculated (ImmigrationDate-DOB).

Reading from left to right, notice each table has a specific ID called a Primary Key. Each Primary Key is related to a specific field in a subsequent (child) table called a Foreign Key. This is how relational databases keep track of related data.

I hope this example can help you get started on the correct path. I have also attached the ACCESS file I created that has every continent already in its respective table.

 

[alvesi]

If you are still interested in suggestions for your database design, here is what I might begin with just based upon what information you provided. The table, field and relationship structure looks like this:
View attachment 107497
Assuming you wish to track where prople came from and where they immigrated to, this structure assumes:

1. Each continent contains multiple countries
2. Each country contains multiple Regions and/or States
3. Each Region/State contains multiple Local Regions and/or Cities
4. Each Local Region or City contains multiple family names
5. Each family name contains multiple Family Individuals who immigrated to a specific continent, country, region or state and local region or city on a certain date. They also have a status (legal or illegal) and Date Of Birth (DOB) so that their age at immigration can be calculated (ImmigrationDate-DOB).

Reading from left to right, notice each table has a specific ID called a Primary Key. Each Primary Key is related to a specific field in a subsequent (child) table called a Foreign Key. This is how relational databases keep track of related data.

I hope this example can help you get started on the correct path. I have also attached the ACCESS file I created that has every continent already in its respective table.

Thank you very much LarryE, I'll add this rational to my structure.

 

[alvesi]

Isac,

Don't be too quick to jump into physical database!! Most database related problems are traced back to design issues.
data 1a and data 1b may be great for you and those intimately familiar with the data, but it's too abstract to offer meaningful advice.

You didn't mention immigration patterns in your initial post. So, based on my more general comment in post#2, and on LarryE's comment, I suggest you step back --forget Access, combo boxes etc- for the moment. What exactly are the Subjects for analyzing "immigration patterns". To immigrate you must emigrate ( to A from B), and what level of detail (LarryE gave an example of "scope"). Is there an ethnicity, religious or other theme to your investigation? Is it immigration to Country X or immigration to various Countries/Continents/...

Just to see if we're on the same "wavelength", I posed this question to ChatGPT:

What subject matter(entities and attributes) would you expect in an analysis of immigration patterns?

Here is the response that I hope helps you with design.

An analysis of immigration patterns could involve various subject matters and entities. Here are some potential entities and attributes that could be considered in such an analysis:

1. Countries of origin: This would involve examining the countries from which immigrants are coming and identifying the number of immigrants from each country.
2. Countries of destination: This would involve examining the countries where immigrants are going and identifying the number of immigrants going to each country.
3. Demographic information: This would involve looking at the age, gender, and other demographic characteristics of immigrants, as well as the reasons for emigrating.
4. Education and skill level: This would involve examining the educational background and skill level of immigrants and assessing the impact of these factors on the immigration patterns.
5. Economic factors: This would involve looking at the economic conditions in both the countries of origin and destination and examining the impact of these factors on the immigration patterns.
6. Immigration policies: This would involve looking at the immigration policies of the countries of destination and assessing the impact of these policies on the immigration patterns.
7. Legal status: This would involve examining the legal status of immigrants in the countries of destination and identifying any patterns or trends in the legal status of immigrants.
8. Social integration: This would involve examining the social integration of immigrants in the countries of destination and identifying any challenges or barriers to integration.

Overall, an analysis of immigration patterns would involve examining a range of entities and attributes related to both the countries of origin and destination, as well as the individual immigrants themselves.

Thank you Jdraw, you are right, and that's the right approach I shall adopt.

 

[alvesi]

It always helps if you use actual field names and then to give actual data for each table rather than
using Data 1a, Data 1b etc

Hi Mike60Smart,

Was my lack of experience. I though cutting out the fat would facilitate the general understanding but then, by all questions, I saw it was the opposite.