Hi i'm hoping i can get some help with my access assignment.
first things first I have a table called jobsheet which is linked to a table called jobsheet/stock one to many JobCode being thr primary key the table jobsheet/stock is a link table between jobsheet and Stock to prevent a many to many relationship. The way it works is by the following.
For every Job there is one or many stock and for every item of stock there may be used for one or many jobs.
example i have a jobcode 10000000 has 2 items of stock nicknamed 1234 price £100 and 5678 at £50 what i'm wating to do is design a query that lets me add those 2 stock values used in the jobsheet it's linked to is this possible without going into SQL coding as i'm a newbie.
I am gratful for any help i am given
Graeme;
If you would post a copy of your database absent of any confidential data. If necessary, post it with dummy data. In your response, scroll down to "Manage Attachments," and follow the instructions. Before you attempt to attach your database, be sure to zip it as this forum does not accept .mdb or .accmdb files. I am sure we can get you set up with the correct query.
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
Hello
I think you may need a third table to manage the potential many-to-many realtionship between table Job and table Stock. Call this third table JobStock, say, and for it create two fields JobCode and StockCode. Make JobCode and StockCode joint key fields for the new table.
Then link one-to-many Job.JobCode <> JobStock.JobCode and link one-to-many Stock.StockCode <> JobStock.StockCode.
Then you have the basis for assigning many stock items to a job, and a stock item to many jobs.
David
Access and Excel Developer | UK
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks