Subform not displaying all records

[toast]

Hello all,

My first post and so my apologies for it being a request for help!

I have 2 tables.
Table 1 contains a list of employees.
Table 2 contains the work shifts.

There are 2 employees per shift, call them 'Person 1' and 'Person 2'.
Employees are referenced by their employee number.

There is a one-to-many relationship between Employee Number and Person 1, and a one-to-many relationship between Employee Number and Person 2.

I would like a form that shows each employee, with a subform showing all shifts they worked.

The problem is that the subform only ever shows the records where the employee was Person 1, never when they were Person 2.

I am sure that I am missing something very simple, but I can't figure it out so any pointers would be very much appreciated!

 

[GinaWhipp]

Welcome to the forum! We are glad to help os no apology needed!

Your problem is you are missing a table...

tblEmployees
tblWorkShifts
tblEmployeeWorkShifts < missing table

Your subform should then be based on tblEmployeeWorkShifts

 

[toast]

Thank you very much for the prompt reply.

I've been reading about junction tables as a means to many-to-many relationships but I am struggling to understand the examples sufficiently to solve my problem - I'll keep at it and return if I really can't make any headway.

Many thanks once again

 

[GinaWhipp]

And I'll be here...

 

[toast]

I am still struggling on this one, but I wonder if perhaps there is a more fundamental problem with what I am attempting to achieve.

Is it possible to have two fields within the same table which are linked to one field in another table? A simple example of what I mean-
tblEmployee with fldName
tblShift with fldShiftLeader and fldShiftDeputy

An employee may be allocated into either field in tblShift, but would never be allocated into both fields within the same record (shift).

Do I need to redesign my tables, or is there nothing wrong with this in principle?

 

[GinaWhipp]

I think it we need an explanation of what you are trying to do? As in, are you simply trying to track which Employee works which Shift or is there more to it?

 

[toast]

Thank you once again for the reply. I am trying to achieve a lot (perhaps too much!)

I need to retrospectively track what occurred on each work shift - who worked the shift (always 2 people), which vehicle they used, how long the shift lasted and how much they used the vehicle.

To complicate matters further, there are multiple locations holding simultaneous shifts.

For each employee I need to store the usual suspects (contact details, title etc) but also keep track of how many hours they have been on duty in the past and how much they have used a vehicle.

 

[GinaWhipp]

Okay, well not too much but if this is your first database then alot to learn. Access has a steep learning curve. Are those the only tables you have? Because you are going to need quite a few more and then you will need to learn about Relational Database Design, see...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html

 

[toast]

Thank you very much for the links.

This is my first foray into access; I've done simple customer - order - product databases in Lotus (blast from the past!) and tracking duty times in HSQL (Openoffice base). I still have plenty to learn but I think I have the raw basics down OK.

I'll try to make further progress and inevitably return for more pointers!

 

[GinaWhipp]

And we will be here...

 

[ootkhopdi]

And we will be here...

i m also facing this problem
i have a table,in which i store all data for persons
i create a query for a person ,to know ,the selected person is membership
when i run query i got the result of all societie(s) for that specific person
when i open the form for that i also get all detail for members
but when i insert members form as a subform in other from it show only first record not all records.

pls tell me what is the prob.

 

[GinaWhipp]

ootkhopdi,

I will need to know your table and form set-up to answer your question properly. Might I also suggest you start your own thread as this one is quite old and will not receive the attention it deserves...

 

[ootkhopdi]

ootkhopdi,

I will need to know your table and form set-up to answer your question properly. Might I also suggest you start your own thread as this one is quite old and will not receive the attention it deserves...

ok
i start a new thread