Normalising Data Help (1 Viewer)

[GendoPose]

Hi All,

I need some help normalising my data properly, and then showing the values in a form.

Currently, my table relationships look like this;

However, there can be multiple Genres per Band and each Genre will be applied to mulitple Bands, and I know this is a Many-To-Many relationship, but I'm uncertain on how to create this properly and then show it in a subform in a form.

Any help at all?

Thanks

 

[jdraw]

Tell us in plain English WHAT your model is intending to represent. The model and data base (tables and relationships) should support your business/requirement -- What is the "requirement"? Clear and simple terms.

 

[GendoPose]

Tell us in plain English WHAT your model is intending to represent. The model and data base (tables and relationships) should support your business/requirement -- What is the "requirement"? Clear and simple terms.

Essentially, there are 2 main forms called Bands and Users.

In frmBands, it shows the BandName, Genres(subform), CurrentMembers(subform) and PreviousMembers(subform).

In frmUsers, it shows FullName, Instruments, CurrentBands(subform) and PreviousBands(subform).

I've managed to make genres into a subform instead of a textbox so I can show multiple genres for each band, however now when I go into the frmUsers, the subform of Current and PreviousBands now shows multiple records, as each band has multiple genres. I imagine this is something to do with the recordsource query but I'm not entirely sure how to fix this.

EDIT; see attached screenshots

 

[jdraw]

No, in plain English, simple "business" terms. No Access forms etc.
You have told us HOW you have done something, we need to know WHAT it is that this "model" represents.

Here's an example from RogersAccessLibrary

ZYX Laboratories requires an employee tracking database.
They want to track information about employees,
the employee's job history, and their certifications.
Employee information includes first name, middle initial,
last name, social security number, address, city, state, zip,
home phone, cell phone, email address. Job history would
include job title, job description, pay grade, pay range, salary,
and date of promotion. For certifications, they want certification type and date achieved.

 

[GendoPose]

I want to see each Band with their name, genres, current members and previous members, and I also want to see each User with their name, instruments, current bands and previous bands.

 

[plog]

tblBands is incorrectly linked to tblGenres. The foreign key is in the wrong table. Instead of a foreign key in tblBands for tblGenres, tblGenres should have a foreign key to tblBands. The same might go for tblInstruments and tblUsers if a user can play multiple instruments.

Additionally, the CurrentMember field is redundant in tblBandMembers. If there is no MemberDateTo value, then the band member is current.

 

[GendoPose]

tblBands is incorrectly linked to tblGenres. The foreign key is in the wrong table. Instead of a foreign key in tblBands for tblGenres, tblGenres should have a foreign key to tblBands. The same might go for tblInstruments and tblUsers if a user can play multiple instruments.

Additionally, the CurrentMember field is redundant in tblBandMembers. If there is no MemberDateTo value, then the band member is current.

I've fixed that by creating a link table for the Genres sub form;

Eventually I will do the same for Instruments but I want to get it right for Genres first and then apply the same rules.

So now I've got it like that, how would I go about changing the repeated records in frmUsers?

 

[plog]

I don't see repeated records in frmUsers

 

[GendoPose]

Well it's essentially showing the same Band each time with the seperate genre, where ideally I'd like just one line with all 3 genres in the same box. I know I could simply get rid of the genre field in the subform, but if there is a way to do what I'd like, I'd much prefer that.

 

[plog]

Is that portion of the form updateable? Do you want users to be able to add/edit/delete bands that the user is/was a part of?

If so, that sub form should be based only on tblBandMembers. You could then use an unbound control on that subforms record line to bring in the Band's genre. If the user can have multiple instruments you will have the same issue on your Band form.

If not, why do you need the genre there? It's the user's form, not the band's form. Put a button beside the band name and let them jump to the band form to see the band's information.

Also, if the user can have multiple instruments you will need a subform for that as well on your users form, instead of a simple drop down.

 

[GendoPose]

Is that portion of the form updateable? Do you want users to be able to add/edit/delete bands that the user is/was a part of?

If so, that sub form should be based only on tblBandMembers. You could then use an unbound control on that subforms record line to bring in the Band's genre. If the user can have multiple instruments you will have the same issue on your Band form.

If not, why do you need the genre there? It's the user's form, not the band's form. Put a button beside the band name and let them jump to the band form to see the band's information.

Also, if the user can have multiple instruments you will need a subform for that as well on your users form, instead of a simple drop down.

The form is more for viewing data rather than data entry, so yes it is updatable but it doesn't matter to me if it isn't. It isn't essential to have the Genre field in there and I've tested it without it and everything works as expected, it'd just be a nice addition to give a little bit more information (the user can already just double click in the BandName and it'll jump to that band in frmBands).

I've now done the same for Instruments, the same as my Genres subform currently is, so the Instruments field isn't in the subform in frmBands.

 

[plog]

Reports are for data viewing. I'd go that route to show all the information I wanted and just use forms for editing data.

 

[GendoPose]

Reports are for data viewing. I'd go that route to show all the information I wanted and just use forms for editing data.

I know, but the idea is that users can jump between forms and records this way, it's also not meant the be the user frontend but the design of a web backend, I've just put the forms in so I can test it all and so the admins and I can easily see what's what.

So is there any way to put those records on one line, or will I just have to omit the Genre and Instrument fields from the subforms?

 

[plog]

There's code floating on this forum about creating a comma seperated string from multiple rows in a query. I think Allen Brown might have some function that does that. Search around for that to see if it is VBA or some query trick.

 

[jdraw]

I want to see each Band with their name, genres, current members and previous members, and I also want to see each User with their name, instruments, current bands and previous bands.

This is a good start, and based on your dialog with plog, I suggest you add the following:

--multiple users (access only or access/update)
- available as web based.

 

[GendoPose]

This is a good start, and based on your dialog with plog, I suggest you add the following:

--multiple users (access only or access/update)
- available as web based.

add these to where sorry?

 

[jdraw]

Your list of requirements.

Your dialog with plog indicates that your database will be multi-user, and it will be web based in some manner. These don't deal necessarily with the subject matter, but they do have implications on design/development.
Perhaps you could tell us more about the web based part of you proposed application.
Are you considering Sharepoint?

 

[GendoPose]

Your list of requirements.

Your dialog with plog indicates that your database will be multi-user, and it will be web based in some manner. These don't deal necessarily with the subject matter, but they do have implications on design/development.
Perhaps you could tell us more about the web based part of you proposed application.
Are you considering Sharepoint?

Oh, I see. Well the actual forms and reports aren't going to form any part of the web backend, just the tables. The forms are just currently meant for testing things work so far, we'll no doubt convert the tables and queries to web-compatible when we role this out.

Besides, the web based part hasn't been started yet, this was just a test until the framework is completed.