Anyone know a good on-line resource for MS Access97?
27 February 2002, 10:19 AM
#1
Scooby Regular
Thread Starter
Join Date: Jun 2000
Posts: 4,413
Likes: 0
Received 0 Likes
on
0 Posts
I've been asked to write a (to me 
) complicated db and need to find some solutioins to a few problems I have in desinging it. Does any one know of an on-line resource I cound use to look for potential solutions?
Thanks
[Edited by Nimbus - 2/27/2002 10:54:54 AM]
) complicated db and need to find some solutioins to a few problems I have in desinging it. Does any one know of an on-line resource I cound use to look for potential solutions?Thanks
[Edited by Nimbus - 2/27/2002 10:54:54 AM]
27 February 2002, 11:14 AM
#3
Scooby Regular
Thread Starter
Join Date: Jun 2000
Posts: 4,413
Likes: 0
Received 0 Likes
on
0 Posts
OK, heres the situation...
I'll be importing a file which contains order, part and qty records. This needs to go into a "master stock table". No problems so far. Then, at the end of the month we will receive a list of parts with qtys that have been removed. I will import this into another table.
What I need the db to do is to go through the list of removals, look for the first record in the "master stock" table with the same part number and subtract the qty from the balance. If the balance reaches zero for that order/part (ie the removal qty > stock qty for that order/part record), then look for the next matching part and write down the qty. Contine to do this until the qty from the removals has been deducted from the matching parts in the "master stock" table.
Does this make sence?
I'm checking if we need to list the records from the removal file that were applied to the master stock record.
cheers
I'll be importing a file which contains order, part and qty records. This needs to go into a "master stock table". No problems so far. Then, at the end of the month we will receive a list of parts with qtys that have been removed. I will import this into another table.
What I need the db to do is to go through the list of removals, look for the first record in the "master stock" table with the same part number and subtract the qty from the balance. If the balance reaches zero for that order/part (ie the removal qty > stock qty for that order/part record), then look for the next matching part and write down the qty. Contine to do this until the qty from the removals has been deducted from the matching parts in the "master stock" table.
Does this make sence?
I'm checking if we need to list the records from the removal file that were applied to the master stock record.
cheers
27 February 2002, 11:32 AM
#5
Scooby Regular
Thread Starter
Join Date: Jun 2000
Posts: 4,413
Likes: 0
Received 0 Likes
on
0 Posts
I was thinking of using one 
It's just the way I need to write it that I need to think about further.
As a little idea of the way this project was given to me...
Jan 21st
Manager - I need you to write this DB program.
Me - Well, I would need to look at the specs, think about the design, build and test. When do you need it for?
Manager - Three weeks.
Me - No way, go too much on to have time to complete by then.
Manager - Ok, we'll get someone on contract to do it.
Feb 25th
Manager - The contractor is too expensive. Can you do a slightly lower spec version?
Me - May be. When do you need it for?
Manger - The end of the week.
Me - <picks self up from floor> No bl**dy chance!!
Manager - OK, how about end of next month?
It's just the way I need to write it that I need to think about further.
As a little idea of the way this project was given to me...
Jan 21st
Manager - I need you to write this DB program.
Me - Well, I would need to look at the specs, think about the design, build and test. When do you need it for?
Manager - Three weeks.
Me - No way, go too much on to have time to complete by then.
Manager - Ok, we'll get someone on contract to do it.
Feb 25th
Manager - The contractor is too expensive. Can you do a slightly lower spec version?
Me - May be. When do you need it for?
Manger - The end of the week.
Me - <picks self up from floor> No bl**dy chance!!
Manager - OK, how about end of next month?
27 February 2002, 11:50 AM
#6
Scooby Regular
Join Date: Jun 2000
Posts: 3,783
Likes: 0
Received 0 Likes
on
0 Posts
You should be able to get it sorted in a morning for that stock update thing.
I'd advise getting your boss to shell out on a good reference book for you - I had a cracking one for MS Access 97 when I was learning the ropes. Hold on, lets see if I can find it...
...No can't find it but was called Access97 bible. Here's the new version...
I'd advise getting your boss to shell out on a good reference book for you - I had a cracking one for MS Access 97 when I was learning the ropes. Hold on, lets see if I can find it...
...No can't find it but was called Access97 bible. Here's the new version...
Trending Topics
27 February 2002, 12:15 PM
#8
Scooby Regular
Thread Starter
Join Date: Jun 2000
Posts: 4,413
Likes: 0
Received 0 Likes
on
0 Posts
Ian,
That would be a great help. Let me know if you can find it
Writing the update quiery is easy, it's just how to make it look at each record in turn, deduct a qty, then move to the next matching one to remove the remaining amount (if any).
As I'm writing/thinking about this I think I can see where this needs to go. I think I'm looking at creating some "holding" feilds where I need to temp store the qtys.
mmmmm, need more brain food
That would be a great help. Let me know if you can find it
Writing the update quiery is easy, it's just how to make it look at each record in turn, deduct a qty, then move to the next matching one to remove the remaining amount (if any).
As I'm writing/thinking about this I think I can see where this needs to go. I think I'm looking at creating some "holding" feilds where I need to temp store the qtys.
mmmmm, need more brain food
27 February 2002, 12:20 PM
#9
Scooby Regular
Thread Starter
Join Date: Jun 2000
Posts: 4,413
Likes: 0
Received 0 Likes
on
0 Posts
PS..
Everything I know about Access97 (not that much) has been self taught. We don't have that much expertise with it in our company which is why I've been asked to do it
Everything I know about Access97 (not that much
) has been self taught. We don't have that much expertise with it in our company which is why I've been asked to do it
27 February 2002, 04:10 PM
#11
Scooby Regular
Thread Starter
Join Date: Jun 2000
Posts: 4,413
Likes: 0
Received 0 Likes
on
0 Posts
Been making some progress..
Managed to create two queries.
First looks for the first record of the removals table.
Second looks for any records in the stock table that match the one in the first query.
Now just need to work out how to do the subracting thing so that it applies it sequentially to the selected records.
Managed to create two queries.
First looks for the first record of the removals table.
Second looks for any records in the stock table that match the one in the first query.
Now just need to work out how to do the subracting thing so that it applies it sequentially to the selected records.
28 February 2002, 09:46 AM
#12
Scooby Regular
Join Date: Jan 2001
Posts: 534
Likes: 0
Received 0 Likes
on
0 Posts
Ok Nimbus,
Query 1 for the import table should group all your identical part numbers together - if you have duplicates, and sum the quantities....
Then you can use the results of this query and join them to the normal table, then run an update.....
E.G.
IMPORT TABLE
PARTNUM QTY
123 4
123 5
124 1
125 5
124 2
Result of 1st Query returns:
PARTNUM QTY
123 9
124 3
125 5
Then
Join this table to the parts table to do the update.....
i'm hoping this makes sense so far, but if you mail me the tables i'll knock up some examples for ya to have a look at, otherwise my post will drivel on and on and on.....
crispin.wright@granadamedia.com
Query 1 for the import table should group all your identical part numbers together - if you have duplicates, and sum the quantities....
Then you can use the results of this query and join them to the normal table, then run an update.....
E.G.
IMPORT TABLE
PARTNUM QTY
123 4
123 5
124 1
125 5
124 2
Result of 1st Query returns:
PARTNUM QTY
123 9
124 3
125 5
Then
Join this table to the parts table to do the update.....
i'm hoping this makes sense so far, but if you mail me the tables i'll knock up some examples for ya to have a look at, otherwise my post will drivel on and on and on.....
crispin.wright@granadamedia.com
Thread
Thread Starter
Forum
Replies
Last Post
Scott@ScoobySpares
Full Cars Breaking For Spares
61
11 January 2021 03:08 PM
Mattybr5@MB Developments
Full Cars Breaking For Spares
33
29 August 2017 07:18 PM
Scott@ScoobySpares
Full Cars Breaking For Spares
7
14 December 2015 08:16 AM
Mattybr5@MB Developments
Full Cars Breaking For Spares
20
22 October 2015 06:12 AM