I would like my Access 2007 Join Query to Assign four Model Numbers (Four Records) in the Event of a Null Date

I have a join query that joins a table of shipped dates, qtys, and model numbers with a table of possible ship dates. This is to create a null (later converted to a zero) for dates where none of these four model numbers shipped.

The problem is, I need a date and a zero, if a null, for each of the four model numbers. if there are shipments for one of the four model numbers, there will be an entry for that date and no nulls for the other three. Short of making four separate queries, one for each model, is there a good way to do this? Thanks.

RexQuality LeaderAsked:
Who is Participating?
Jeffrey CoachmanMIS LiasonCommented:
I could not DL your file because this site throws an error if the extension(.txt) does not match the contents (.accdb)
You can upload .acdb file directly here now.

In any event here is an example of what you requested.
I used two (nested) queries (qryAllProdsAllDays, then qryFinal), here so you can see the thought processs.

The kicker was the final GroupBy to get the unique values...

There may be a more efficient approach, but this was how I thought it through...

Have fun!


[ fanpages ]IT Services ConsultantCommented:

It may help me/others understand your problem (better) if you could provide the SQL syntax you have already & also the structure/content of the referenced tables.



RexQuality LeaderAuthor Commented:
The first is the SQL of the query. The four models I want to extract shipment totals for are in the table Lynn_XSite_Models_tbl . It has one column called Model_Number, which is Text.

I used this approach because users will frequently ask me for data on various models. Four is small. I had six requests last month for lists of model numbers close to 200 models. The join technique was the easiest way I could come up with to selectively pull info on so many criteria from the raw data table.

In this case, the raw data table is Raw_Shipment_Import_Append. In that table, the column that contains the Model_Number data is called Material (It is named that in SAP, the source of this data that I export and append to this access table, Raw_Shipment_Import_Append) In addition, there are date fields, always corresponding to a week ending Saturday, that give the week of the shipments. There is also a Billing_qty field that gives the qty shipped.

The problem is the Nulls. Some of the Records in the Raw_Shipment_Import_Append table have no data (shipments) for a model for a given week. I can't have gaps in the data. It will be charted and I need a continuous x axis of weekls.

To try and get that, I am trying to do a second join with the table Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl, which is the table created by the previous Join query, and a table of all the possible week ending dates called Possible_Week_Ending_Dates_tbl. It has one column called PossibleWeekEndingDates that is a continuous list of all the posisble Saturdays for the next ten years.

The problem is that I have, in this case, four model numbers. If there were no shipments for ANY of those four in a given week, I get one null for shipments and one PossibleWeekEndingDate date. I want the query to return a null for each of the model numbers that had no shipments. For example, if there were shipments for two of the model numbers in a given week, I would get those two model numbers and their data for that week. What I would want would be those two model numbers and their data for that week PLUS two Nulls for that week for the other two model numbers.  

Again, this is so that I can have a continuous X scale. The only way I know ho to do this is to create a separate query for each of the model numbers. That is a slight pain with four model numbers, but a real deal breaker with seven sets of 200 model numbers each.

Does that make any more sense?

SELECT Raw_Shipment_Import_Append.Material, GetWeekEndingDate(Raw_Shipment_Import_Append.Calendar_Day) AS Shipped_Week, Sum(Raw_Shipment_Import_Append.Billing_Qty) AS SHIPPED_QTY, Raw_Shipment_Import_Append.Month_and_Year INTO Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl
FROM Raw_Shipment_Import_Append INNER JOIN Lynn_XSite_Models_tbl ON Raw_Shipment_Import_Append.Material=Lynn_XSite_Models_tbl.Model_Number
GROUP BY Raw_Shipment_Import_Append.Material, GetWeekEndingDate(Raw_Shipment_Import_Append.Calendar_Day), Raw_Shipment_Import_Append.Month_and_Year
ORDER BY GetWeekEndingDate(Raw_Shipment_Import_Append.Calendar_Day);

Open in new window

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RexQuality LeaderAuthor Commented:
Correction...that is the SQL of the Query that creates Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl.

The SQL I have, so far, for the query that would join that table with the list of possible saturdays is below.
SELECT Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl.Material, Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl.Shipped_Week, Sum(NZ(Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl.SHIPPED_QTY)) AS ShippedQTY, Possible_Week_Ending_Dates_tbl.PossibleWeekEndingDates INTO Lynn_D_50_Xsite_Models_Shipped_Joined_Week_qry
FROM Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl RIGHT JOIN Possible_Week_Ending_Dates_tbl ON Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl.Shipped_Week=Possible_Week_Ending_Dates_tbl.PossibleWeekEndingDates
GROUP BY Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl.Material, Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl.Shipped_Week, Possible_Week_Ending_Dates_tbl.PossibleWeekEndingDates
ORDER BY Possible_Week_Ending_Dates_tbl.PossibleWeekEndingDates;

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
...how about just posting a sample database...?
RexQuality LeaderAuthor Commented:
Is 2007 OK, or do I need to convert to .mdb?
Jeffrey CoachmanMIS LiasonCommented:
If the DB is originally in 2007, then post in 2007.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

RexQuality LeaderAuthor Commented:
OK. Here is the 2007 version.

If you run the query Lynn_C_50_Xsite_Models_Shipped_by_Week_qry it makes the table Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl. (I am going to have to ODBC connect this to Minitab, and Minitab must have a table to connect to, so the query alone is not sufficient)

As you can see from Lynn_C_50_Xsite_Models_Shipped_by_Week_tbl, there are several weeks with no shipments for some of the Model numbers. I want to Join this with Possible_Week_Ending_Dates_tbl, but I will only get ONE Null per Date, even if there are more than one Model Mumbers with Nulls.

Desired Output of this query:  One entry per each model number per each week. If that means 4 nulls, 2 nulls, no nulls, that is what I am trying to generate.

Thanks for any help.
RexQuality LeaderAuthor Commented:
Thank you. I really do appreciate the help. That is VERY close to what I need. I do have one additonal criteria that I am not sure how to fold in to what you created. I adapted your query to my db and got the below results... boag2000 query results
That gives the nulls (or results if there were any) for each of the four model numbers. However, notice the gaps between the dates. I also need to join the dates with a possible week ending date table such as below...again...because I need a continuous weekly axis possible week ending dates table
because of the way your query works, I qould assume this join would have to take place before the query you made that pulled in all of the available ProdIDs

Jeffrey CoachmanMIS LiasonCommented:
Well your "Additional Criteria" was never mentioned in your original post.

My post illustrates a way to get you the output you wanted based on what you posted...
RexQuality LeaderAuthor Commented:
It is a complicated question. I tried to descibe it as best I could. Obviously I failed. I have no problem making that a new question. Thanks.

RexQuality LeaderAuthor Commented:
Thank you
Jeffrey CoachmanMIS LiasonCommented:
Just to be clear (and hopefully fair)...

I was in no way trying to "Force" you to accept my post.

Yes, I know that sometimes in a complex task, important info is overlooked...

I just felt that the complexity of the question as posted, matched the quality of my post, hence this question could stand on it's own.

Posting a new, related question would create a logical progression, ...therefore be more useful to another member searching for the same issue.

Sound fair?

RexQuality LeaderAuthor Commented:
Very fair. I did not feel at all pressured. I really, really appreciate all the help you've given me. It is a double handicap to have such limited programming skills as I do...not only can I not solve my own problems, but I frequently don't know how to even adequately convey my problem to others.

No problem on my end. Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.