a Challenge for me

[Naveed Pathan]

dear friends i have a problem that i can't sort it out though i have tried
alot anyhow i am here to get help from you people the problem i have is

suppose if i have a serial of numbers and if i sell a particular serial out
of that so i want to know that which serial is in my hand for example

if i have serial from 1 to hundred and i sell number 88 so i want to know
that number 88 is no more availabe in the whole serial as it is sold out .

Note : i will be thankful if you people sort it out or give an ultinative
suggestion or solution for it thankx

 

[KARL DEWEY]

How are you storing the serial numbers?
How are you recording the sales?

Post your table structure with field names and data type. Post sample data.

 

[Naveed Pathan]

ok suppose to be the purchases serial is like

Item Serial Start Serial End
Cobra 562400405500 562400405600


now suppose that i sold the product with serial

item serial Start serial End
Cobra 562400405560 562400405569

so i sold that ten products so what are the remaining and shouldn't suppose
to show the one i sold out.
thankx

 

[Naveed Pathan]

a challenge isn't it yes it is

ok suppose to be the purchases serial is like

Item Serial Start Serial End
Cobra 562400405500 562400405600


now suppose that i sold the product with serial

item serial Start serial End
Cobra 562400405560 562400405569

so i sold that ten products so what are the remaining and shouldn't suppose
to show the one i sold out.
thankx

 

[Fred]

For cases like your example you will need inventory tables which treat your
items individaully.

Presumably there are many units (with many serial numbers) whihc have the
same model number.

Here's an idea for your main tables:

ProductTable

Field: Product_ID Autonumber, primary key
Field: ManufacturerName
Field: ModelNumber


ItemTable

Field: Item_ID Autonumber, primary key
Field: Product_ID Integer (foreign key, linked to field of same name in
previous table
Serial Number

This sect table will ned a record for each individual part.

 

[Naveed Pathan]

it won't help try to understand what i mean thankx

 

[Steve]

Using your example, create a table with the numbers 1 to 100. Create an
unmatched query using the unmatched query wizard that includes that table
and your sales table that contains the serials you sold. The unmatched query
will return the serials in the 1 to 100 table that are not in your sales
table. To create an unmatched query, open to the list of queries and click
New. One of the options will be to open the unmatched query wizard.

Steve
(e-mail address removed)

 

[Naveed Pathan]

thanks for the reply i already know what you told me but i am confused about
the question i have asked it could you please the serial number in my example
then you will know understand yourself that what i mean and that can't be
done through match or unmatch queries. that's why i am saying a
challenge for everybody to sort it out

thankx

 

[Steve]

Naveed,

Please reword your question and I will take another stab at it.

Steve
(e-mail address removed)

 

[Fred]

Dear Naveed,

I believe that I do understand what you are saying. And, if you will
forgive me, that I may have thought through the possibilities within what you
are saying more than you have, and so might understand what you are asking
even better than you do.

Is this correct:

You are saying that you typically start with a group opf items of the same
type, but each with a different serial number, and typically a set of
sequential serial numbers. And that any one of these items may get sold
/removed. (e.g you can't dictate that they take the lowest or highest serial
number.)

And, you have given us an example, where someone has taken one out of the
series and want the datebase to show you whic ones are still in stock. And
since the PARTICULAR example that you have in mind (just one missing) is
where the missing one is an exception, you are imaging a system which will
serve for that example, I.e. to say "1 through 87" and 89 thought 100" are in
stock. And so you are imaging some systems that will base it's answer
based on where the (one) "gap" is. You probably haven't thought about the
more complex scenerios, e.g where someone has removed the following serial
numbers: 3,5,8,11, 22-29, 35, 39, 45--49. 51. 55.56. 61, 70-75, 85,86,
93, 94. And, you have probably made the common error of thinking that this
can be solved without starting from the ground up by desigin a table
structure that will support accomplishing what you are trying to do. I.E.
making the mistake that you can skip this table structure step and go right
to the finish line of what you seek via some query or form action.

If all of my guesses are correct, then my previous post would give you a
foundation that would be a good starting point for solving this. More
work would be needed after that, but it would be a foundaiton.

 

[Naveed Pathan]

i reallly appreciate your help thanks for all of you but still let make it
more clear i am not satisfied what i want ....... let make it more clear i
will have two tables; one for purchases and the second for sales to give you
the idea about the stock .

for example [purchases table]

SNO Item Serial Starts
Serial Ends

1 Talk Home 87564561
87565060


so the above stock is 500 cards with me in stock lets supppose i sold the
following 60 cards randomly in [sale table ] such as

SNO Item Serial Starts
Serial Ends

1 Talk Home 87564581
87564590
2 Talk Home 87564640
87564690


however there are two transaction now in the sale table where in the first
transaction i sold 10 cards while in the second transaction i sold 50 cards
so the question is that how i would know in the system that which cards
serial number are in my hand when slowly i sale more cards.
hopefully it is more clear now

thankx in advance

 

[PieterLinden via AccessMonster.com]

Which part of "you are going about it the wrong way" did you not understand?
Basically, what you are asking is not possible given the restrictions you
have put on the solution. There is no other way to determine which items are
missing from a list when you don't have a list. That's what the table of
numbers is about. If you have that, you can do a simple outer join and
determine the missing members. Sure, you can write some code to add records
to your table, but that only solves the immediate problem.

So how about you take everybody's advice and do what they tell you and then
try from there instead of telling us that we don't know what you mean?
Wouldnt that be more productive?

Allen Browne has a nice example of Quantity On Hand on his website.
www.allenbrowne.com

Start there and then see how far you get.

 

[Naveed Pathan]

i reallly appreciate your help thanks for all of you but still let make it
more clear i am not satisfied what i want ....... let make it more clear i
will have two tables; one for purchases and the second for sales to give you
the idea about the stock .

for example [purchases table]
SNO Item Serial Starts Serial Ends
1 Talk home 87564561 87565060





which are 500 cards in the stock. if i sell the cards randomly such as 50
and 10 so what would be the stock serial in hand then.

SNO Item Serial Starts Serial Ends
1 Talk home 87564581 87564590
2 Talk home 87564640 87564690

however there are two transaction now in the sale table where in the first
transaction i sold 10 cards while in the second transaction i sold 50 cards
so the question is that how i would know in the system that which cards
serial number are in my hand when slowly i sale more cards.
hopefully it is more clear now

thankx in advance

 

[Naveed Pathan]

still it is a challenge for me particularly and for you as well. coz our
database can be successfully run on those methodes.

i reallly appreciate your help thanks for all of you but still let make it
more clear i am not satisfied what i want ....... let make it more clear i
will have two tables; one for purchases and the second for sales to give you
the idea about the stock .

for example [purchases table]
SNO Item Serial Starts Serial Ends
1 Talk home 87564561 87565060





which are 500 cards in the stock. if i sell the cards randomly such as 50
and 10 so what would be the stock serial in hand then.

SNO Item Serial Starts Serial Ends
1 Talk home 87564581 87564590
2 Talk home 87564640 87564690

however there are two transaction now in the sale table where in the first
transaction i sold 10 cards while in the second transaction i sold 50 cards
so the question is that how i would know in the system that which cards
serial number are in my hand when slowly i sale more cards.
hopefully it is more clear now

thankx in advance

Naveed,

Please reword your question and I will take another stab at it.

Steve
(e-mail address removed)





.

 

[Gina Whipp]

Naveed.

I do not think you are understanding the answers you are getting and why the
solution may be simple in your mind but it is not simple for a database to
understand.

Your Purchase table...
SNO Item Serial Starts Serial Ends
1 Talk home 87564561 87565060


Your Sales table
SNO Item Serial Starts Serial Ends
1 Talk home 87564581 87564590
2 Talk home 87564640 87564690

Normally in a Purchase table (or what we would here describe ans in
Inventory table) you would have a Quantity or Stock column which would let
you know who many items you have in stock or how many items had been
purchased. Then in the Sales table there would be a couln to indicate
QuantitySold. You could then easily do a query to show the amount sold and
update your Purchase table to deduct the amount Sold thereby creating an
updated Quantity in your Purchase table.

However, because of the way you choose to set up your tables the *only*
solution that comes to mind is creating a query (name it qryPurchases...

SELECT Purchases.SNO, Purchases.Item, Purchases.[Serial Start],
Purchases.[Serial End], Val([Serial End])-Val([Serial Start]) AS OnHand,
Left([Serial Start],5) AS MatchTo
FROM Purchases;

Then for you next query (name it qrySales)...

SELECT Sum(Val([Serial End])-Val([Serial Start])) AS Sold, Left([Serial
Start],5) AS MatchTo
FROM Sales
GROUP BY Left([Serial Start],5);

And for you final query (you can name that what ever you like)...
SELECT qryPurchases.SNO, qrySales.MatchTo, qryPurchases.Item,
[OnHand]-[Sold] AS Balance
FROM qryPurchases INNER JOIN qrySales ON qryPurchases.MatchTo =
qrySales.MatchTo;

However, it should be noted, the above will only work if the data is like
what you provided, if not then the queries have to be redone. And I will
stress that *this* solution is NOT the way it should be done. You really
need to consider the *great* advice you have already gotten that your tables
need to be restructered to fit a normalized database design. What you are
doing is forcing a square peg in a round hole.

Good Luck!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

still it is a challenge for me particularly and for you as well. coz our
database can be successfully run on those methodes.

i reallly appreciate your help thanks for all of you but still let make
it
more clear i am not satisfied what i want ....... let make it more clear
i
will have two tables; one for purchases and the second for sales to give
you
the idea about the stock .

for example [purchases table]
SNO Item Serial Starts Serial Ends
1 Talk home 87564561 87565060





which are 500 cards in the stock. if i sell the cards randomly such as 50
and 10 so what would be the stock serial in hand then.

SNO Item Serial Starts Serial Ends
1 Talk home 87564581 87564590
2 Talk home 87564640 87564690

however there are two transaction now in the sale table where in the
first
transaction i sold 10 cards while in the second transaction i sold 50
cards
so the question is that how i would know in the system that which cards
serial number are in my hand when slowly i sale more cards.
hopefully it is more clear now

thankx in advance

Naveed,

Please reword your question and I will take another stab at it.

Steve
(e-mail address removed)


message
thanks for the reply i already know what you told me but i am
confused
about
the question i have asked it could you please the serial number in my
example
then you will know understand yourself that what i mean and that
can't be
done through match or unmatch queries. that's why i am
saying a
challenge for everybody to sort it out

thankx

:

Using your example, create a table with the numbers 1 to 100. Create
an
unmatched query using the unmatched query wizard that includes that
table
and your sales table that contains the serials you sold. The
unmatched
query
will return the serials in the 1 to 100 table that are not in your
sales
table. To create an unmatched query, open to the list of queries and
click
New. One of the options will be to open the unmatched query wizard.

Steve
(e-mail address removed)


message
dear friends i have a problem that i can't sort it out though i
have
tried
alot anyhow i am here to get help from you people the problem i
have
is

suppose if i have a serial of numbers and if i sell a particular
serial
out
of that so i want to know that which serial is in my hand for
example

if i have serial from 1 to hundred and i sell number 88 so i want
to
know
that number 88 is no more availabe in the whole serial as it is
sold
out .

Note : i will be thankful if you people sort it out or give an
ultinative
suggestion or solution for it thankx


.



.