Need some help with table references, specifically on forms.

[Sama8525]

Ok now that i'm in front of the database and have some of your answers let me try to add some visual aids and more explanations.



I've attached a screenshot of a form called "sponsorships". That is my end goal (obviously with the data populating tables in a way that's usable later. On the main section of the form there are fields for Brand Id (Combobox reference to the sponsor company), Dept ID (should be CompanyId, reference to Sponsored Company), Sponsorship type (Reference to SPType table), Cost (USD amount for total sponsorship), and 3 notes fields for general info. All of that is easy and a direct pull from the sponsorships table.

Where i run into issues is the subform (or whatever i need to replace it with). Below the above mentioned fields i need a list of departments (labeled as "internal teams") for that sponsorship. Those departments will need to be only departments that are part of the Sponsored company selected above, there could be a list from 0 to many of them for each sponsorship. So for example for Sponsorship 1 between Sponsor A and company 2 there could be 3 of company 2's departments involved, in sponsorship 2 between Sponsor A and Company 2 there could be 8 of company 2's departments involved, and Sponsorship 3 between Sponsor A and Company 2 there could be generically with company 2 with no departments involved (so 0).

What i need it to do is to be able to fill out what sponsor and company the partnership is between, then let me add multiple rows of departments of the selected company. then somehow save that in tables somehow. When i cycle between records on the form, I'd like it to cycle between sponsorships showing a list of the depts already logged. I will also be adding brands (which are a subset of the sponsors same as how departments are part of companies), but i assume i can just replicate the solution for departments for that (however you might see some references to it in the relationships picture)

I've also added a screenshot of my relationships table, which i recognize is a mess. I'm just trying to get this functioning and then will be doing some renaming etc to clean it up. This will be used as a log of data, not as the back end for software or anything, so i just need to be able to record stuff and reference it later/report on it. My knowledge of Access ends at sort of 1 degree of complexity (tables/forms/reports/queries and directly referencing each other) so i'm a little lost here. there is no data at all in any of this so I'm perfectly able to destroy/rebuild it however needed. Thanks all

[mike60smart]

Hi

Can you upload a zipped copy of the database?
It would make it easier to give help.

[Sama8525]

Esports Sponsorship Tracking.zip

Hi

Can you upload a zipped copy of the database?
It would make it easier to give help.

Sorry for the delay. Attached is the field, which is probably a mess.

[ssanfu]

All I've done is to rearrange the Relationship View.

I renamed Fields - should not use spaces, punctuation or special characters (exception is the underscore) in object names.
Also, having "Id" as the PK field name of every table is, well, not good.

The suffixes:
GameID_PK (ID = a numeric field - PK = Primary Key field)
GameID_FK (ID = a numeric field - FK = Foreign Key field)
(the above is part of my naming convention)

[accesstos]

What about this schema:



I have added the table tblDeptSPs

In the form for the sponsorships, you can use pairs of listboxes to add/remove departments/brands to a sponsorship.

[Sama8525]

All I've done is to rearrange the Relationship View.

I renamed Fields - should not use spaces, punctuation or special characters (exception is the underscore) in object names.
Also, having "Id" as the PK field name of every table is, well, not good.

The suffixes:
GameID_PK (ID = a numeric field - PK = Primary Key field)
GameID_FK (ID = a numeric field - FK = Foreign Key field)
(the above is part of my naming convention)

Thanks that helpful. Do you have a copy of the file you edited?

[Sama8525]

What about this schema:



I have added the table tblDeptSPs

In the form for the sponsorships, you can use pairs of listboxes to add/remove departments/brands to a sponsorship.

How do i filter for only those departments/brands that are in the Sponsors/Esport orgs that were selected in the form?

[accesstos]

How do i filter for only those departments/brands that are in the Sponsors/Esport orgs that were selected in the form?

I suggest you to try to work [and think] with queries and not with tables directly. Also, try to forget the multivalue fields. We store the data in tables, in single, pure values and we get information via queries. It's best practice to bind always your forms and reports in queries, even if you don't need additional information.

About your question, a query with the table of sponsored departments, joined with the table of sponsorships, gives you the information that you need, like the sponsor and the esport org of a given sponsorship, so you can apply filters on those fields.

About the last part of title of this thread, because a sample is many thousand of words, take a look in attached sample db. In form "frmSPs", select a sponsorship and add/remove departments/brands by double clicking on the corresponding item of the relevant lists. Inspect the queries, the structure and the code of the forms.

Also, i suggest you to follow the Steve's suggestions about the naming convention of your database objects.

Good luck with your project.

Cheers,
John