Lets say i have 2 tables (ok i really do). One is tblShipping, the other is tblShippingDetails. I need to run a query to find all of the maximum values of a qty backordered, so that i can find out what is left to so that i can multiply it by its price and determine how much is still left to ship, but for all open orders. We do partial shipments, and i am not recording the ship values on another table, it is appending the original table, because so many of the values change.
So I decided that i would find the maximum backordered qty, subtract it from the original qty, then multiply what is left by the unitprice. I know how to write a temp table, use dmax("QTYBackOrder","tblShippingDetails","JobNumbe r ='" & [this is the problem part]) to get the value from the details table, how do i then move to the next job number to look for the max "qtybackorder". I am not sure how to look for the next job number to write to a temp table so that i am just reading a table with all of the max quantities.
Should i first sort the table, if i do, how do i move to the next different JobNumber. I hope i have given enough information. If i knew how to find the max for each jobnumber with a query(wizard of course, sql scares me, lol) then i would just use the qry as the record source, but i couldnt figure it out. I couldnt figure out what criteria to use for job number..... or how to word it.
Any help is greatly appreciated. If i havent given enough information, feel free to tell me.
7 2714 NeoPa 32,557
Recognized Expert Moderator MVP
I suspect the info is all (mostly) there K, but the problem is that the order it's presented in is so random that it's very difficult to work with.
I don't understand why you're looking for a maximum value rather than a sum, if you're trying to determine what has been shipped.
Your line of code however, should be constructed differently depending on what type of field [JobNumber] is.
You don't say how you have access to the required JobNumber value, so I shall assume, for the sake of clarity, that you have a variable with it stored in. If it is a string value then this will be strJobNo : - ... = DMax("[QTYBackOrder]","[tblShippingDetails]","[JobNumber]='" & strJobNo & "'")
If it is numeric, then it will be lngJobNo : - ... = DMax("[QTYBackOrder]","[tblShippingDetails]","[JobNumber]=" & lngJobNo)
Sorry,
I missed something very easy there, that you pointed out. I have (with a query)taken the first jobnumber record (there are multiples, due to the fact that we sometimes ship partial orders) and the sum of the qty shipped, i dont know why i was trying to do this the hard way.....Thanks NeoPa.
Btw - I could have inserted the criteria for jobnumber, but i needed to run the query on all records.
NeoPa 32,557
Recognized Expert Moderator MVP
I don't pretend to understand you clearly K, but if I've helped then I'm pleased.
I do get the impression that you're now processing with a query rather than a single (or even multiple) call(s) to a Domain Aggregate function (DMax). If so, that certainly seems a more sensible approach.
Success with your project.
Just for a little clarification: I have reports for the president and multiple vice presidents that let them know what the $ value of what we have shipped for the month is. Then they wanted the value of what was still in house, for all open orders that have either not shipped or have not completely shipped. I know i was a little random, but i was having trouble, and beleive it or not just by you saying the word "SUM" helped me tremendously.
My original method was query, i was having trouble with it and decided i had more options if i used code, with dlookups and create table, and append queries, while looping.....(not ever thinking about summing the actual shipped qty's..... brain fart). But i have the query finished and it all seems very simple now.
Sometimes i just need a person that understands access to bounce questions off of, and make me think for myself. Need a job? lol
Thanks again.
NeoPa 32,557
Recognized Expert Moderator MVP @kstevens
I'm absolutely not kidding when I say that's exactly the same for me.
Sometimes, just talking to someone who has the intelligence to nod wisely as I stream my thought processes out, can help to clarify things enormously. If they actually know something (even just a little) about what I'm saying as well, that's a bonus.
PS. Thankfully, I'm not looking for alternative employment at this (very difficult) time.
I agree with you. If I think the person I am talking to MAY (even with low probability) be able to answer the question, I can answer half of them myself. But if I ask my Teddy Bear, it doesn't work.
I get half my questions answered by creating the questions for this forum. I never actually have to submit them, 'cause I know the answer.
Part of it is that if I am going to ask someone else, I have to actually formulate the question, and get rid of all those partial/fuzzy ideas running around in my head. I never post in haste.
NeoPa 32,557
Recognized Expert Moderator MVP
No you don't. I always know if a thread has your name on it then it will tax me. Always the question is fully explained too.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: asad |
last post by:
Hi, i have some problem in showing data.
I have two tables
First table(containing Hosting categories)
Second table(Containg catagories data lik
url,keyword,description.....etc)
I want to...
|
by: Scott Gerhardt |
last post by:
Hello, I am new to the list, my apology if this question is beyond the
scope or charter of this list.
My questions is:
What is the best method to perform an aggregate query to calculate
sum()...
|
by: dmisen |
last post by:
I have data on air pollution coming from sources across the country,
and control options for reducing pollution from each source. The
example below shows data for:
- 2 sources (plant_ID "001" and...
|
by: David |
last post by:
Hi,
I'm having trouble copying table data to new records.
I have two tables as follows:
*** Specifications (Table)
specification_ID (field) LINKED
product_ID (field)
specification_header...
|
by: gps |
last post by:
I'm trying to use Dmax to dip into a my invoice header table and pull the maxium invoice number out of it.
I'm running Access 2007 on Vista Business, but had the same prob with Access 2003 on...
| |
by: jacc14 |
last post by:
Hi
This is the second query I have had with Dmax. The first one I resolved as it was in a form and by using DMAX everytime I click for new record it gave a new order number ie dmax("ordernumber",...
|
by: Richnep |
last post by:
Hi all,.
I have a small issue I would like to resolve with VBA. I have a form
which
it's fields are bound to a table. Within that form is a subform which
is a
subtable.
Tables relationship...
|
by: kstevens |
last post by:
I have tables setup with a main table for information and a subtable that records the "multiple" records for the main record. I have written a query to go in and find Null or "" values to delete...
|
by: DanicaDear |
last post by:
I have a report named "rptHOTSTICKS_EXPIRING" based on a query named "HOSTICKS_SHIPPING_REPORT Query". The query contains these fields:
ORDER_NUM (text)
CUST_NUM (text)
Name, address, contact...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |