A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

a Challenge for me



 
 
Thread Tools Display Modes
  #1  
Old December 18th, 2009, 04:11 PM posted to microsoft.public.access.tablesdbdesign
Naveed Pathan
external usenet poster
 
Posts: 61
Default a Challenge for me

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
  #2  
Old December 18th, 2009, 05:22 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default a Challenge for me

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.

--
Build a little, test a little.


"Naveed Pathan" wrote:

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

  #3  
Old December 18th, 2009, 05:45 PM posted to microsoft.public.access.tablesdbdesign
Naveed Pathan
external usenet poster
 
Posts: 61
Default a Challenge for me

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" wrote:

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

  #4  
Old December 18th, 2009, 09:15 PM posted to microsoft.public.access.tablesdbdesign
Naveed Pathan
external usenet poster
 
Posts: 61
Default a Challenge for me

a challenge isn't it yes it is


"Naveed Pathan" wrote:

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" wrote:

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

  #5  
Old December 18th, 2009, 10:05 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default a Challenge for me

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" wrote:

a challenge isn't it yes it is


"Naveed Pathan" wrote:

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" wrote:

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

  #6  
Old December 19th, 2009, 06:16 PM posted to microsoft.public.access.tablesdbdesign
Naveed Pathan
external usenet poster
 
Posts: 61
Default a Challenge for me

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

"Fred" wrote:

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" wrote:

a challenge isn't it yes it is


"Naveed Pathan" wrote:

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" wrote:

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

  #7  
Old December 19th, 2009, 06:59 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default a Challenge for me

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



"Naveed Pathan" wrote in 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



  #8  
Old December 20th, 2009, 02:53 PM posted to microsoft.public.access.tablesdbdesign
Naveed Pathan
external usenet poster
 
Posts: 61
Default a Challenge for me

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" wrote:

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



"Naveed Pathan" wrote in 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



.

  #9  
Old December 20th, 2009, 04:27 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default a Challenge for me

Naveed,

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

Steve



"Naveed Pathan" wrote in 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

"Steve" wrote:

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



"Naveed Pathan" wrote in 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



.



  #10  
Old December 21st, 2009, 02:32 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default a Challenge for me

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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.