Solved

Think I need another table

Posted on 2011-03-07
11
217 Views
Last Modified: 2012-05-11
Hi there,
I have done a few databases in MS Access 2007 and I normally do OK when coming up with a structure and relationships. etc.

A friend of mine has also done one for his new venture, but is having trouble and asked me if I could help him out. Like a fool I said I would have a look at it. :)

He uses "part number" to seach for stock and not description or category, if that part number is not in stock he thinks he has none. but if the exact same item is sourced from a different supplier, it has a different part number and therefor he has stock of the item, just not the same part number.
If I make a form bound to "tblItems" (see attached showing table design), I can put an unbound combobox on it based on part numbers and when a part number is selected, it opens that particular item record, and I can get the form to show me what stock I have, but how can I get the form to show me what stock I have of the same item which has a different part number? In other words, If I have no stock of a certain part number how can I view all other part numbers that relate to the exact same item?

I'm probably missing something simple, but I just can't seem to grasp it.

I have attached a picture of my table design so far. I think I may need another table just for part numbers, but I am unsure of how to link it to the items table.. (all primary keys are autonumber)  
Dbase.png
0
Comment
Question by:Stephen Byrom
  • 6
  • 3
  • 2
11 Comments
 
LVL 21
ID: 35059729
I like to use a junction table that allows tblItems record to be related (self join) other records in the same table  

0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 35059823
Are you saying I should create a junction table that joins to another instance of tblItems and use that junction table as the source of my search form?
0
 
LVL 39

Expert Comment

by:als315
ID: 35059920
You can try to do as in included sample (may be tlpkItemType is not correct table, but it uses idea of joining same items in table)
DB26868860.zip
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
ID: 35059926
Almost.

Yes the junction table will link tblItems to tblItems.

For record source or row source I use a union query. The first Select will get the record that matches the part number. Then union with a select that finds any record linked  via the junction table to the part number.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 35060113
Hmmm.
Not used Union before, I'll have to look it up. Not sure what you mean

ps, Als315, cannot open your file.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 39

Assisted Solution

by:als315
als315 earned 250 total points
ID: 35060222
Try this one DB26868860.zip
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 35060411
Thanks als315, I managed to open that one.
I see that the subform is based on a select query but the "tlkpItemType" is a lookup table to categorise the items, (about 8 categories), but an idividual item can have the exact same properties except Part number.
I maybe not explaining myself very clear. It's difficult to try and put across what I mean
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 35060662
One way of looking at it is this.
Each item has a unique primary key and Part number but that item can be duplicated in every aspect except the primary key and part number.
Maybe I should link the part number of each item in the table "tblItems" to another table consisting of just part numbers on a one to many basis?
Or am I making trouble for myself?
0
 
LVL 39

Expert Comment

by:als315
ID: 35066912
You can use table like tlkpItemType in my example and same parts in tblItems will have field with reference to this table and same items will have same reference, then you will be able to select similar parts.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 35071478
Thanks for your time on this, I will look into your suggestion later this evening when I get home and let you know.
Thanks again for your time.
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 35072524
Thank you both for your time. I went with a Junction table, (self join) as the coach suggested and also gleened some insight on query building from als. I combined both and managed to get a form to show what I was after. So I thought it only fair to award points to each of you.

Thank you both for your time.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now