weird table/query/form/wte issue

[Jester1147]

First, i want to say thanks for the help i have received so far... its been quite positive. However, i think that due to the circumstances of the type of db i have been asked to construct, i may have found myself backed into a corner. Instead of using dummy terms, i will use the actual names that im using so yall can get an idea of what needs to be figured out.

I have a master table that pretty much stores everything. The PK is titled Serial# (yes, its an asset style db). Now, when ever these assets are issued out to personnel they are required to sign a hand receipt for these items. Obviously with the Serial# there is a Nomenclature that goes with it. Each serial# is then assigned to an individual. Sometimes there comes an event to where one individual can have multiple serial numbers (no more than 2 due to the nature of the asset). Now i have figrued out how to create a query to where the individual's name is shown with all assigned assets, but how do i go about showing BOTH (again no more than 2 assets) Serial#s + Nomenclatures on one form in order (like a datasheet but in the dead center of a form to where the layout of the form remains). There are a series of other fields necessary for this form but ih ave it situated and just need the ability to show the multiple records.

i will try to clear up anything i can. Thanks.

 

[dkinley]

I think I know what it is you are trying to do and hopefully can assist.

First, though, the PK being called Serial# may or may not cause problems now or later ... why not use SerialNum to avoid any potential pitfalls? (I would also advise against using SerialNo to limit confusion and because this may refer to a Yes/No field that you may want to use later).

I've also include this and this link to help you moving forward.

Asset db's normally use self-referring tables to capture the parent/child asset link; however, since I am unsure of how your db is structured - I will offer only a cosmetic solution to what you are doing.

If you could guarentee that you would never have more than 2 assets per person (or record) and if both assets are in the same table and linked (not by the self-referring link just described) to the Person primary key then you could use a subform holding both assets information (master/child is person ID primary/foreign).

You could use a continuous form view in the subform and allocate enough space in your main form to present the information elegantly. If both assets use different fields (and controls) then using a single-form view for the subform should do the trick for you.

In the end, you are simply embedding the subform into the main form for presentation purposes without the user knowing that there is a subform.

HTH,
-dK

 

[Jester1147]

Thanks for the info... i will give it a try.

You mentioned about if i could guarantee that no more than 2 would be assigned, yes. The database is for weapons issue and the personnel would never be given more than 2 weapons (pistol/rifle) except for extreme circumstances (of which would have definitely handled seperately and the data would be entereted direcly into the datasheet and not via the form).

There is infact only one table with the data in question. The other tables being used are for merely combo boxes for predefined info (nomenclature has a seperate table stating M9, M4, M16, and GAU-5P) to be selected since these are the only inventoried weapons. The different Case type is also on the same kind of set up.

That being said... coudl this in any way effect your previous statement?

 

[dkinley]

Potentially. It is in accordance to the level of detail that you are wanting to record.

Suppose, for instance some personnel were issued some M16s with scope and others not. I don't know if this could be a potential situation in your scenario but in some of the units I served with, this was the case. The scopes we had were also serialized, thus one could want to track that as well. If, however, the armory tracked this information and issued the rifle as a unit (under its serial) then at the unit level, one might only want to track the serial of the rifle - which it seems you want to do.

Now, not incorporating deuce gear into this system the aforementioned method I discussed could be implemented. One table, People, another table, Asset. The primary key PeopleID would have a foreign key in the Asset table.

Then, you can set your asset form as a subform in the People form. The master/child links for the form would be the PeopleID.

If you could foresee, for any reason, that the system may grow (such as inclusion of duece gear) then you would want to create a Weapon table and set a foreign key for this in the Asset table. This would make the asset table a junction table that you could use for other asset types.

-dK

 

[Jester1147]

the scopes are tracked serially, however that is it (FYI, i have a mobility/contingency weapons account for maintenance personnel, so no extra gear would be serially issued).

I was able to get the subform in to display properly except for one factor, When the subform itself is pulled up, the two fields (different serials with associated nomenclature) are viewed correctly. When the subform is pulled up with the main form, it is not showing correctly, merely one serial duplicated and then one nomenclature (not both).

Ever see anything like this before? is it a subform property that i am messing up?

 

[dkinley]

Is something like the attached what you are trying to do?

-dK

 

[Jester1147]

its something similar, but the form is to not be editable

here is a stripped copy of the db (obviously, the personal infomration needed to be removed)

 

[dkinley]

Understood on the stripped down portions. Which form are trying to embed the subform to?

-dK

 

[Jester1147]

"Multigun Subform1" sits in all of the "AF1297" forms in the same spot. The form has been stripped down to just the two fields to maintain the structure and stability of the form.

 

[dkinley]

Okay ... here is what I see. Trying to think of the best way to explain this.

You are linking the subform by serial number. Thus in the query you are displaying two serial numbers but the form is only for one serial number so the subform only displays one item.

You could link it by the persons name and that would work, but if there are duplicate names it will not work because they aren't unique (such as a record primary key would be).

In the sample I put together, the person record drives the system because I assume that once a person is assigned serialized assets then if the person is deployed, those serial assets go with that person.

I am attempting breakdown what you have and post up a potential fix ...

-dK

 

[dkinley]

I guess my questions is ... are you more interested in managing just the equipment and where it is? Or the people, who they are, the equipment they have, and where they are?

I ask this to determine to do one side of this - once you see it, you should be able to work it from the other side so you can have both.

-dK

 

[dkinley]

What is the M4 table? I understand what the 1297 is but not clear on what the M4 record is.

Thanks!
-dK

 

[Jester1147]

actually the subform is linked by the Deployer's name and not the serial number... which is why the Table M4 has two entries, two serial numbers, one name. The part that has the M4 listed is the nomenclature. Eventually, there will be 4 different weapon systems loaded up. The M4 Table is the master table holding ALL of the information.

I am trying to manage the weapon and the individual that weapon was assigned to. The master premise of the program is to manage the weapon. The purpose of the 1297 is for the individual assigned the weapon(s).

 

[dkinley]

Okay ... this isn't it because I am sort of lost from not understanding the process (we used ECR cards). But I did do some stuff .. maybe it will get you started.

I attempted to normalize your data somewhat, but got lost around the M4 form. The table names I used begin with a t, the queries a q, and forms an f (to use a standard naming structure to limit confusion) so it should be easy to seperate what I have done so far from what you have.

If you look at q_fSomeForm - this would probably be the build up for 1297 - however, you can see where you can just grab data at will to create what you need. Also, through normalization you can check out the (restarted) fM4 form and see how I made use of normalization without having to reenter Sgt SoAndSo's name over and over and over again.

I also attempted to give you some sort of semblance of display in fPeople so you can see where I was going, because if you can do this, then you can do the same if a fWeapons form was created (as I mentioned before).

You need to create junction tables to arrive at your final goal - the 1297 card. I attempted to do this with the M4 form. All of the bits of data coming together through the foreign keys to create this.

If done correctly, you will not need another form to select the type of 1297 to be presented, the system will automatically know.

I also put in an Add Weapons button to give you an idea. Through validation you can cause this to disappear if a person has been assigned two weapons (DCount).

Anyhow ... take a look and you can see some of the overhauling that needs to be completed, hopefully this will get you started.

Post back for more questions. I probably won't be around for a couple of days, but the people here are far more experienced than I and can probably answer your questions much more articulately than I. I will respond when I get back ....

-dK