equipment inventory query

[Russ via AccessMonster.com]

Please help, looking for good and accurate inventory data. We ship out our
equipment to external locations within the company and need to see what our
current inventory is, and help to accomplish this would be greatly
appreciated

I have an asset table and a shipping details table.

Asset ID
Asset Description

Shipping ID
Asset Id
Shipping Date
Receiving Date

What can I do to see a list of all assets currently in stock or if a specific
asset is in inventory or is still out? I guess if I can get help on the first
part I can figure out the last.

 

[GBA]

well depends on your logic but in general terms;

to find 'in stock'

a. where Shipping Date Is Null (never been shipped)

or

b. where Shipping Date AND Receiving Date are < Today's date


to find 'in field'

kind of depends on when the Receiving Date is populated - if this is
populated at the time of shipping and is an accurate value - then you only
need to query on where Receiving Date is > Today's date

 

[Russ via AccessMonster.com]

Thanks, but I think some how I have to compare the two tables; asset and
shipping.

I assume you are suggesting a query on the shipping table. One problem I
could see is; what if I have a piece of equipment that has never shipped, it
would be in my asset table but no record in the shipping table so the query
would not show it. So how would you inventory it?

well depends on your logic but in general terms;

to find 'in stock'

a. where Shipping Date Is Null (never been shipped)

or

b. where Shipping Date AND Receiving Date are < Today's date

to find 'in field'

kind of depends on when the Receiving Date is populated - if this is
populated at the time of shipping and is an accurate value - then you only
need to query on where Receiving Date is > Today's date

Please help, looking for good and accurate inventory data. We ship out our
equipment to external locations within the company and need to see what our

[quoted text clipped - 14 lines]

asset is in inventory or is still out? I guess if I can get help on the first
part I can figure out the last.

 

[John W. Vinson]

Thanks, but I think some how I have to compare the two tables; asset and
shipping.

I assume you are suggesting a query on the shipping table. One problem I
could see is; what if I have a piece of equipment that has never shipped, it
would be in my asset table but no record in the shipping table so the query
would not show it.

You can change the join type on the Join line in the query: select the join
line in the query design window, view its properties, and select the option
that says "Show all records in Assets and matching records in Shipping".

 

[Russ via AccessMonster.com]

Thanks John,

So you are suggesting making a query with the two tables tblAssets and
tblShipping.

Using the fields from the tblAssets;
AssetID
AssetDescription

Using the fields from the tblShipping;
AssetID
ReceivingDate
With a criteria (ReceivingDate is null)

In the query Join the AssetId from both tables then change the join type by
selecting the join line, view its properties, and select the option that says
"Show all records in Assets and matching records in Shipping".

Or

Do I have to make a query called “ReceivingDateIsNull “ to filter the
shipping table showing all assets with criteria (ReceivingDate is null)

Using the fields from the tblShipping;
AssetID
ReceivingDate
With a criteria (ReceivingDate is null)

Now build new query with the table tblAssets and the query called
“ReceivingDateIsNull;

Using the fields from the tblAssets;
AssetID
AssetDescription

Using the fields from the query ReceivingDateIsNull;
AssetID
ReceivingDate
With a criteria (ReceivingDate is null)

Now In the query Join the AssetId from the table and query
“ReceivingDateIsNull†then change the join type by selecting the join line,
view its properties, and select the option that says "Show all records in
Assets and matching records in query ReceivingDateIsNull ".

Which way will give me the most accurate information or are they the same?

I appreciate everyone’s help!

Thanks, but I think some how I have to compare the two tables; asset and
shipping.

[quoted text clipped - 3 lines]

would be in my asset table but no record in the shipping table so the query
would not show it.

You can change the join type on the Join line in the query: select the join
line in the query design window, view its properties, and select the option
that says "Show all records in Assets and matching records in Shipping".

 

[GBA]

yes, that is what needs to be done.

2 queries; one 'in stock' and the other 'in field'

in both cases the query is of both tables - and the 2 tables are joined by
the common Asset ID field.

you can of course then put both query results in a single Report - if you
need a single paper output...or screen look - - but that's a different topic.

experiment to get the correct results in the query(s) - but really it more
or less depends on the < or > or null of the key date fields compared to
today's date.

Thanks John,

So you are suggesting making a query with the two tables tblAssets and
tblShipping.

Using the fields from the tblAssets;
AssetID
AssetDescription

Using the fields from the tblShipping;
AssetID
ReceivingDate
With a criteria (ReceivingDate is null)

In the query Join the AssetId from both tables then change the join type by
selecting the join line, view its properties, and select the option that says
"Show all records in Assets and matching records in Shipping".

Or

Do I have to make a query called “ReceivingDateIsNull “ to filter the
shipping table showing all assets with criteria (ReceivingDate is null)

Using the fields from the tblShipping;
AssetID
ReceivingDate
With a criteria (ReceivingDate is null)

Now build new query with the table tblAssets and the query called
“ReceivingDateIsNull;

Using the fields from the tblAssets;
AssetID
AssetDescription

Using the fields from the query ReceivingDateIsNull;
AssetID
ReceivingDate
With a criteria (ReceivingDate is null)

Now In the query Join the AssetId from the table and query
“ReceivingDateIsNull†then change the join type by selecting the join line,
view its properties, and select the option that says "Show all records in
Assets and matching records in query ReceivingDateIsNull ".

Which way will give me the most accurate information or are they the same?

I appreciate everyone’s help!

Thanks, but I think some how I have to compare the two tables; asset and
shipping.

[quoted text clipped - 3 lines]

would be in my asset table but no record in the shipping table so the query
would not show it.

You can change the join type on the Join line in the query: select the join
line in the query design window, view its properties, and select the option
that says "Show all records in Assets and matching records in Shipping".

 

[Russ via AccessMonster.com]

Sorry, I am just not getting it

in my shipping table I have
shipId, assetID, shipdate, recievedate
Shipdate = date it was shipped out from inventory
recievedate = date it returned back into stock (so the field is null while it
is out at the job)

If I make a query on the shipping table looking for records where recievedate
Is Not Null assuming since it has a receiving date it must be in stock.
But is coming back with too many records, I am missing something.

yes, that is what needs to be done.

2 queries; one 'in stock' and the other 'in field'

in both cases the query is of both tables - and the 2 tables are joined by
the common Asset ID field.

you can of course then put both query results in a single Report - if you
need a single paper output...or screen look - - but that's a different topic.

experiment to get the correct results in the query(s) - but really it more

Thanks John,

[quoted text clipped - 54 lines]

 

[GBA]

well...... Is Not Null is going to return every record almost...unless it
has never shipped and thus not a populated field...

I can't give perfect advice because I don't know if both Ship & Receive
Dates are populated correctly at the same time - and whether they are subject
to change, such as late returns, also whether you clear out the data when
returned in stock so that these fields are then made null - - or whether the
old data stays there until a new ship request, and then is written over. So
this below is with a grain of salt and you need to tweak....

Receive Date < Today's Date = in stock

or Receive Date Is Null = in stock

Receive Date > Today's Date = in field

Sorry, I am just not getting it

in my shipping table I have
shipId, assetID, shipdate, recievedate
Shipdate = date it was shipped out from inventory
recievedate = date it returned back into stock (so the field is null while it
is out at the job)

If I make a query on the shipping table looking for records where recievedate
Is Not Null assuming since it has a receiving date it must be in stock.
But is coming back with too many records, I am missing something.

yes, that is what needs to be done.

2 queries; one 'in stock' and the other 'in field'

in both cases the query is of both tables - and the 2 tables are joined by
the common Asset ID field.

you can of course then put both query results in a single Report - if you
need a single paper output...or screen look - - but that's a different topic.

experiment to get the correct results in the query(s) - but really it more

Thanks John,

[quoted text clipped - 54 lines]

line in the query design window, view its properties, and select the option
that says "Show all records in Assets and matching records in Shipping".

 

[John W. Vinson]

Thanks John,

So you are suggesting making a query with the two tables tblAssets and
tblShipping.

Using the fields from the tblAssets;
AssetID
AssetDescription

Using the fields from the tblShipping;
AssetID
ReceivingDate
With a criteria (ReceivingDate is null)

In the query Join the AssetId from both tables then change the join type by
selecting the join line, view its properties, and select the option that says
"Show all records in Assets and matching records in Shipping".

Or

Do I have to make a query called “ReceivingDateIsNull “ to filter the
shipping table showing all assets with criteria (ReceivingDate is null)

Using the fields from the tblShipping;
AssetID
ReceivingDate
With a criteria (ReceivingDate is null)

Now build new query with the table tblAssets and the query called
“ReceivingDateIsNull;

Using the fields from the tblAssets;
AssetID
AssetDescription

Using the fields from the query ReceivingDateIsNull;
AssetID
ReceivingDate
With a criteria (ReceivingDate is null)

Now In the query Join the AssetId from the table and query
“ReceivingDateIsNull” then change the join type by selecting the join line,
view its properties, and select the option that says "Show all records in
Assets and matching records in query ReceivingDateIsNull ".

Which way will give me the most accurate information or are they the same?

Both are absolutely accurate for what they do - but they are different!

The Left Join will return a NULL value for all fields in tblShipping if there
is no matching record in tblShipping. So your first query will return all
records in Assets for which either (a) there is a shipping record with a NULL
Receiving date OR (b) there is no record in shipping whatsoever.

The second query will return only those records for which there IS a shipping
record with a null value in the ReceivingDate.

 

[Russ via AccessMonster.com]

GBA, Thanks for trying to help, I appreciate you taking your time out to help!


The ship date and receive date are actual dates.

Let me try and explain…
When we do a ship ticket, it appends all information into a shipping (history)
table.

Say we shipped out a piece of equipment #1232 the shipping table would have a
record like this;
ShipId #999, EquipId # 1232, ShipDate 2/2/09, receivedate (Is Null)

then when it returns we do a receiving ticket we look up that record from the
shipping (history) table via the shipId & equpID and update the record with
the receivedate
ShipId #999, EquipId # 1232, ShipDate 2/2/09, receivedate 4/2/09

So, the shipping table would look like this

would mean that it is OUT (there is a blank receivedate)
shipId, assetID, shipdate, recievedate
999 1232 2/2/09
888 1232 1/2/09 1/31/09
777 1232 12/2/08 12/31/08

would mean that it is IN (there is no blank receivedate)
shipId, assetID, shipdate, recievedate
999 1232 2/2/09 4/2/09
888 1232 1/2/09 1/31/09
777 1232 12/2/08 12/31/08

Am I going about it wrong?

well...... Is Not Null is going to return every record almost...unless it
has never shipped and thus not a populated field...

I can't give perfect advice because I don't know if both Ship & Receive
Dates are populated correctly at the same time - and whether they are subject
to change, such as late returns, also whether you clear out the data when
returned in stock so that these fields are then made null - - or whether the
old data stays there until a new ship request, and then is written over. So
this below is with a grain of salt and you need to tweak....

Receive Date < Today's Date = in stock

or Receive Date Is Null = in stock

Receive Date > Today's Date = in field

Sorry, I am just not getting it

[quoted text clipped - 27 lines]

 

[GBA]

that's straight forward....

you shouldn't have any problem with it...

GBA, Thanks for trying to help, I appreciate you taking your time out to help!


The ship date and receive date are actual dates.

Let me try and explain…
When we do a ship ticket, it appends all information into a shipping (history)
table.

Say we shipped out a piece of equipment #1232 the shipping table would have a
record like this;
ShipId #999, EquipId # 1232, ShipDate 2/2/09, receivedate (Is Null)

then when it returns we do a receiving ticket we look up that record from the
shipping (history) table via the shipId & equpID and update the record with
the receivedate
ShipId #999, EquipId # 1232, ShipDate 2/2/09, receivedate 4/2/09

So, the shipping table would look like this

would mean that it is OUT (there is a blank receivedate)
shipId, assetID, shipdate, recievedate
999 1232 2/2/09
888 1232 1/2/09 1/31/09
777 1232 12/2/08 12/31/08

would mean that it is IN (there is no blank receivedate)
shipId, assetID, shipdate, recievedate
999 1232 2/2/09 4/2/09
888 1232 1/2/09 1/31/09
777 1232 12/2/08 12/31/08

Am I going about it wrong?

well...... Is Not Null is going to return every record almost...unless it
has never shipped and thus not a populated field...

I can't give perfect advice because I don't know if both Ship & Receive
Dates are populated correctly at the same time - and whether they are subject
to change, such as late returns, also whether you clear out the data when
returned in stock so that these fields are then made null - - or whether the
old data stays there until a new ship request, and then is written over. So
this below is with a grain of salt and you need to tweak....

Receive Date < Today's Date = in stock

or Receive Date Is Null = in stock

Receive Date > Today's Date = in field

Sorry, I am just not getting it

[quoted text clipped - 27 lines]

line in the query design window, view its properties, and select the option
that says "Show all records in Assets and matching records in Shipping".

 

[Russ via AccessMonster.com]

Seemed easy to me too, but I am doing something wrong getting too many
records back when I run the query.

that's straight forward....

you shouldn't have any problem with it...

GBA, Thanks for trying to help, I appreciate you taking your time out to help!

[quoted text clipped - 50 lines]

 

[GBA]

well - - in your query just use the single shipping table.....and with
results....sort by Asset ID... right click on the Asset table and
delete/remove it from the query design...

only you can tell what is wrong.... you say too many? are there repeating
Asset IDs?

if it looks ok with just the shipping table then it is the join that is
causing your problem.

re-add the Asset Table. Make the relationship from the Shipping Table to
the Asset Table and then rt click on the relationship line and set its
properties so that it is All of Shipping and only those of Asset that match.
It will change to an arrow pointing from Shipping to Asset.

then rerun your query.

Seemed easy to me too, but I am doing something wrong getting too many
records back when I run the query.

that's straight forward....

you shouldn't have any problem with it...

GBA, Thanks for trying to help, I appreciate you taking your time out to help!

[quoted text clipped - 50 lines]

line in the query design window, view its properties, and select the option
that says "Show all records in Assets and matching records in Shipping".

 

[Russ via AccessMonster.com]

yes duplicated i.d.'s

Just for curiosity, how can I achieve a master list showing status?
Something like this
AssetID, Status
12345 In Because receivingdate Is not null
45678 out Because receivingdate Is null
55577 In Because receivingdate Is not null
77755 In Because receivingdate Is not null
99988 out Because receivingdate Is null

Tried to use
Status: IIf(IsNull([RecDate]),"OUT","IN")

But getting both in and out for the i.d. I assume since there is both a
record with the receiving date being null and one that it is not null

well - - in your query just use the single shipping table.....and with
results....sort by Asset ID... right click on the Asset table and
delete/remove it from the query design...

only you can tell what is wrong.... you say too many? are there repeating
Asset IDs?

if it looks ok with just the shipping table then it is the join that is
causing your problem.

re-add the Asset Table. Make the relationship from the Shipping Table to
the Asset Table and then rt click on the relationship line and set its
properties so that it is All of Shipping and only those of Asset that match.
It will change to an arrow pointing from Shipping to Asset.

then rerun your query.

Seemed easy to me too, but I am doing something wrong getting too many
records back when I run the query.

[quoted text clipped - 8 lines]

 

[GBA]

if there are not duplicate IDs in the table; then you should not get them in
your query result. This is what must be resolved. My prior message
addresses this issue.

yes duplicated i.d.'s

Just for curiosity, how can I achieve a master list showing status?
Something like this
AssetID, Status
12345 In Because receivingdate Is not null
45678 out Because receivingdate Is null
55577 In Because receivingdate Is not null
77755 In Because receivingdate Is not null
99988 out Because receivingdate Is null

Tried to use
Status: IIf(IsNull([RecDate]),"OUT","IN")

But getting both in and out for the i.d. I assume since there is both a
record with the receiving date being null and one that it is not null

well - - in your query just use the single shipping table.....and with
results....sort by Asset ID... right click on the Asset table and
delete/remove it from the query design...

only you can tell what is wrong.... you say too many? are there repeating
Asset IDs?

if it looks ok with just the shipping table then it is the join that is
causing your problem.

re-add the Asset Table. Make the relationship from the Shipping Table to
the Asset Table and then rt click on the relationship line and set its
properties so that it is All of Shipping and only those of Asset that match.
It will change to an arrow pointing from Shipping to Asset.

then rerun your query.

Seemed easy to me too, but I am doing something wrong getting too many
records back when I run the query.

[quoted text clipped - 8 lines]

line in the query design window, view its properties, and select the option
that says "Show all records in Assets and matching records in Shipping".

 

[Russ via AccessMonster.com]

when I say duplicate I mean like this...
shipId, assetID, shipdate, recievedate
999 1232 2/2/09
888 1232 1/2/09 1/31/09

If I use the = recievedate is null it only brings back the one record, but
not give me a summary list indicating it's status i.e. if it is in or out.

Just for curiosity, how can I achieve a master list showing status?
Something like this, the I.D. number should only show up once, and an
indicator showing it's status as in or out.
AssetID, Status
12345 In Because receivingdate Is not null
45678 out Because receivingdate Is null
55577 In Because receivingdate Is not null
77755 In Because receivingdate Is not null
99988 out Because receivingdate Is null

Tried to use
Status: IIf(IsNull([RecDate]),"OUT","IN")

all that gave me is...
shipId, assetID, shipdate, recievedate, Status
999 1232 2/2/09 Out
888 1232 1/2/09 1/31/09 IN

I need a sigle line
AssetID, Status
1232 In or out?

Hope | am explaining it well enough.

if there are not duplicate IDs in the table; then you should not get them in
your query result. This is what must be resolved. My prior message
addresses this issue.

yes duplicated i.d.'s

[quoted text clipped - 35 lines]