Assigning quantities to locations

Using Access 2007 but need to save in Access 2003 format to share the database.

I want to create a section of an existing database with a fixed total number
of various assets, where I can then assign quantities of these assets to
'locations' and also to 'case numbers'. Location and case number need to be
seperately assigned and assets can be in both a 'location' and a 'case
number' but not in two of the same. Location, case number and asset type are
predefined in seperate tables with other information attached.
Basically i want to be able to say:

i have 10 of asset X
3 of asset X are in location A
7 of asset X are in location B
5 of asset X are in case C
5 of asset X are in case D

All of these values need to be editable at any stage.

Any help would be greatly appreciated. Please bear in mind that I have
taught myself how to use Access and I am certainly not a programmer.

Thank You

On Mon, 2 Feb 2009 15:30:01 -0800, rmoule
wrote:

I am confused. You wrote: "assets can be in both a 'location' and a
'case
number' but not in two of the same". Isn't your example one where
assets are in two of the same? Please explain.

-Tom.
Microsoft Access MVP


Using Access 2007 but need to save in Access 2003 format to share the database.

I want to create a section of an existing database with a fixed total number
of various assets, where I can then assign quantities of these assets to
'locations' and also to 'case numbers'. Location and case number need to be
seperately assigned and assets can be in both a 'location' and a 'case
number' but not in two of the same. Location, case number and asset type are
predefined in seperate tables with other information attached.
Basically i want to be able to say:

i have 10 of asset X
3 of asset X are in location A
7 of asset X are in location B
5 of asset X are in case C
5 of asset X are in case D

All of these values need to be editable at any stage.

Any help would be greatly appreciated. Please bear in mind that I have
taught myself how to use Access and I am certainly not a programmer.

Thank You

Sorry. To clarify: a single asset can be in both a location and a case
number but cannot occur in two locations or two case numbers. However a type
of asset can occur in more than one location or case number.
Another way of looking at it - the total quantity of an asset in all
locations (or case numbers) cannot be greater than the total quantity of that
asset.

Hope this clears things up a bit

"Tom van Stiphout" wrote:

On Mon, 2 Feb 2009 15:30:01 -0800, rmoule
wrote:

I am confused. You wrote: "assets can be in both a 'location' and a
'case
number' but not in two of the same". Isn't your example one where
assets are in two of the same? Please explain.

-Tom.
Microsoft Access MVP


Using Access 2007 but need to save in Access 2003 format to share the database.

I want to create a section of an existing database with a fixed total number
of various assets, where I can then assign quantities of these assets to
'locations' and also to 'case numbers'. Location and case number need to be
seperately assigned and assets can be in both a 'location' and a 'case
number' but not in two of the same. Location, case number and asset type are
predefined in seperate tables with other information attached.
Basically i want to be able to say:

i have 10 of asset X
3 of asset X are in location A
7 of asset X are in location B
5 of asset X are in case C
5 of asset X are in case D

All of these values need to be editable at any stage.

Any help would be greatly appreciated. Please bear in mind that I have
taught myself how to use Access and I am certainly not a programmer.

Thank You

Hi,
To clarify you clarification Hopfully I understand and will try to
give a real world example.
I think of the case number like a lawyer, the asset can be to a particular
case and therefore can be in a location that is different from other assets
of the same type. so

You can have
AssetID CaseID LocationID Quantity
1 C A 2
1 C B 3
1 D A 1
1 D B 4

"Case D has assets both in location A and B"

So that is the table you need (tblAssetLocations) if you want it in one
table and use the first 3 colums as the "key"

or you want to (just read it may be better)
You use 2 or more tables and have
tblACLConnector
ACLID
AssetID
CaseID
LocationID

tblAssetInformation
ACLID - FK
Quantity
DisposalDate
....

Either way - you have a table that shows the asset, case, and location; and
you can change the Quantity.

You said you were a non-Programmer - so setting up the subform and
relationships will be another question if you don't already know how to do
that.


"rmoule" wrote:

Sorry. To clarify: a single asset can be in both a location and a case
number but cannot occur in two locations or two case numbers. However a type
of asset can occur in more than one location or case number.
Another way of looking at it - the total quantity of an asset in all
locations (or case numbers) cannot be greater than the total quantity of that
asset.

Hope this clears things up a bit

"Tom van Stiphout" wrote:

On Mon, 2 Feb 2009 15:30:01 -0800, rmoule
wrote:

I am confused. You wrote: "assets can be in both a 'location' and a
'case
number' but not in two of the same". Isn't your example one where
assets are in two of the same? Please explain.

-Tom.
Microsoft Access MVP


Using Access 2007 but need to save in Access 2003 format to share the database.

I want to create a section of an existing database with a fixed total number
of various assets, where I can then assign quantities of these assets to
'locations' and also to 'case numbers'. Location and case number need to be
seperately assigned and assets can be in both a 'location' and a 'case
number' but not in two of the same. Location, case number and asset type are
predefined in seperate tables with other information attached.
Basically i want to be able to say:

i have 10 of asset X
3 of asset X are in location A
7 of asset X are in location B
5 of asset X are in case C
5 of asset X are in case D

All of these values need to be editable at any stage.

Any help would be greatly appreciated. Please bear in mind that I have
taught myself how to use Access and I am certainly not a programmer.

Thank You

To Mroule I think that CraigH missed the fact that between your two posts you
used two different conflicting definitions of "asset". I'm assuming that
your second post is the correct one, i.e. that you have individual assets,
and the you have asset types. And so everything you said in your first post
was about asset types, not assets.

If I'm right, then you probably need Asset, AssetType, Case and Location
tables. And each asset (not asset type) can have only one location, type
and and case. (?) In which case your "main" table is the Asset table, and
it has FK's for the other three tables and links to them.

The problem with not using real world information is that it gets confussing:

So:

I have 10 pigs 4 are African Pygmy (Named A-D) and 6 are Juliani Pig (E-J)
A,B,C & F,G are branded Credo (case)
D & E, H,I,J are branded Dredge

AssetID CaseID LocationID Quantity
1 C A 2 A,F
1 C B 3 B,C,G
1 D A 1 D
1 D B 4 E H, I J

"rmoule" wrote:

Sorry. To clarify: a single asset can be in both a location and a case
number
A pig can have location A and Case C, or B and C ...

but cannot occur in two locations or two case numbers.

Pig A is only in A and Case C
Pig E is only in B and Case D

However a type
of asset can occur in more than one location or case number.

An african pigs are in locations A and B (Pig A,B - C)
and Julians have C and D (G - H) Africans ( A - D)

Another way of looking at it - the total quantity of an asset in all
locations (or case numbers) cannot be greater than the total quantity of that
asset.


"Fred" wrote:

To Mroule I think that CraigH missed the fact that between your two posts you
used two different conflicting definitions of "asset". I'm assuming that
your second post is the correct one, i.e. that you have individual assets,
and the you have asset types. And so everything you said in your first post
was about asset types, not assets.

If I'm right, then you probably need Asset, AssetType, Case and Location
tables. And each asset (not asset type) can have only one location, type
and and case.

And that is what I showed with the AssetID it is not the Type of Asset. And
now I see the confusion you say when I said type - that 'type' was "pig" not
African. If you see my table I use AssetID and not AssetTypeID

(?) In which case your "main" table is the Asset table, and
it has FK's for the other three tables and links to them.


But if you are saying that you should put the FK's of the other table into
the Asset table that is incorrect.

You need the linking table to get all the possibilities of Multiple
locations and cases (and then the Quantities). The only FK that should be in
the Asset Table is the AssetTypeID

Craig

Hello Craig,

I think that it all boils down to first clarifying the real world situation.

My presumption (right or wrong) was that when dealing with individual assets
(not assets types) that none of the real world relations to individual
assets are "many to many"


Fred