Solved

how to pull data from more than one table in a report

Posted on 2015-01-25
34
49 Views
Last Modified: 2016-02-10
I have a MS Access 2003 DB.... there are 2 tables and one form.  The form is used to create an order and the products are selected from a drop down based on a 2nd table. TableCL  The form's data is saved in another table, TableMain.

All is working with regards to the form and pulling data from the 2nd table to populate the form for the users selection.  The form is saving to Table 1, no problem.

I am trying to make a report counting orders based a field in Table one... which I am able to do.  The tricky part is that in this report, which is basically summing and counting total order I need one more compare and that is where my confusion lies.  While I am able to apply my needed filter to get the report data, I need to filter it one more step using data based on Table 2... which is based on one of the data items of table 2.  How can I parse though this 2nd table to find a criteria (MinorderQty) based on the data in table 1's order qty?

Please let me know if any clarification is needed, as I am sure it is.  I am not a expert VBA programming especially when it comes to reports.
0
Comment
Question by:thandel
  • 19
  • 14
34 Comments
 
LVL 14

Expert Comment

by:ThomasMcA2
Comment Utility
We may need to see your code, but a subquery can be used to limit summary records, like this:

SELECT SUM(FieldA) ...
FROM TableA AS A
WHERE ...
GROUP BY CustomerNum
HAVING SUM(FieldA) > (SELECT MinOrderQty
                       FROM TableB AS B 
                       WHERE A.CustomerNum = B.CustomerNum)

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
"MinorderQty", ...by what?
By customer?, by Order?, by year?, by Product?

Can you post a graphical example of the exact output you need?
0
 

Author Comment

by:thandel
Comment Utility
Thank... so with some other code I have in the DB I've been trying to use this function:

Function ShipMinFind(CLBrand As String) As Integer

    Dim rs As DAO.Recordset
       
    Set rs = CurrentDb.OpenRecordset("Select * from tCl where CL = '" & CLBrand & "'", dbOpenForwardOnly)
    With rs
        If .EOF Then
            'Me.ODVendorDisplay = Null
            'Me.ODTypeDisplay = Null
            'Me.ODRebate = Null
            'Me.ODvCode = Null
            ShipMinFind = Null
        Else
            'Me.ODVendorDisplay = !Vendor
            'Me.ODTypeDisplay = !Type
            'Me.ODRebate = !Rebate
            'Me.ODvCode = !vCode
            If Me.ODBrand.Value = Me.OSBrand.Value Then
                ShipMinFind = !ShipMinQty / 2
            Else
                ShipMinFind = !ShipMinQty
            End If
        End If
        .Close
    End With
    Set rs = Nothing
             
End Function

I then try to filter my report with the following filter but I'm not having much luck:

((tmain.DateEnter) >= StartDate)  AND ((tmain.DateEnter) <= EndDate) AND ((tmain.Trials) = false) AND (ShipMinFind(tmain.ODBrand) => tmain.ODQty)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
To me at least, ...it is still not clear what is happening here...
Can you post a simplified sample database that exhibits this issue, ...and post an explicit example of the exact results you are looking for?
0
 

Author Comment

by:thandel
Comment Utility
Thank you, I've attached a DB.  I think I cleaned out all but the tables and the report.  Basically in the CL Table there is a MinShipQty... I'm trying to pull order only when the ODQty and/or the OSQty is >= to this MinShipQty.

The report is broken up by "prepby", the person that created the order.  I'm trying to create a report broken down by order creater that the order has  ODQty and/or the OSQty is >= to this MinShipQty.

Thank you.
TEST.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Still lost...

Your two tables should be related, yet they are not..?

ShipMinQty for ID 2 is 12, ...yet there is no corresponding ID=2 in the main table,,,?

So again show me the *exact* results you are looking for in this report..., and explain how this result was determined.

JeffCoachman
0
 

Author Comment

by:thandel
Comment Utility
In order to pull up the min shipping qty on my form I have used code to search for a match as the ODBrand in table one would match the data  (CL) in the CL table.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I have used code to search for a match as the ODBrand in table one would match the data  (CL) in the CL table.
Why not just create a relationship between the two tables?, then no code is required.
You could also probably use dlookup to get this value, ...something similar to this...
=Dlookup("ShipMinQTY","TCI","CL=" & "'" & txtODBrand & "'")

Also note that there are no matches on either of these two fields for the records on the report...
...so I am not quite sure what you need the result to be.

I am not quite sure why this system is designed in this way,...
I am not quite sure I understand the design of the report...
Finally, you still have not given me a clear graphical example of the exact output you need for this report...

But in general, ...if you need to
pull data from more than one table in a report
...The tables should related, ...no code should be needed.

We are at a stand still here...
Perhaps you could explain to me the purpose of his report in simple terms, ...like so:
I have a table of_____________ that represents ___________
I also have another table of_______________ that represents ___________
I need a report that displays _____________________________
For example, Customer ___ has_____ in one table
They also have____ in another table _______
I want the report to display ______________
For example for Customer ____  in the report, they should have a record that looks like this:
_______________________________________________________________________


JeffCoachman
0
 

Author Comment

by:thandel
Comment Utility
Thanks Jeff

I have a table of order that represents customer information with product information selected from Table2

I also have another table of products that represents prodcuts and some specififics, ex. min qty for a year supply

I need a report that displays all data from the first table where the qty of ODQty => MinShipQty from table 2 for a given product

For example, Customer ___ has_____ in one table (??? I Don't follow what you need)

They also have____ in another table _______  (??? I Don't follow what you need)

I want the report to display all orders from the first table that meet this criteria.
0
 

Author Comment

by:thandel
Comment Utility
I think I know how to use DLookup in VBA code for a form but  Iam not sure how to use it to filter report data.

DLookup("ShipMinQty", "tCl", "CL = " & "'" & Me.ODBrand & "'")

Ideally I think I need

ME.ODBrand  = should be change to compare ODBrand from Table one to Table two to filter a report???
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
DLookup("ShipMinQty", "tCl", "CL = " & "'" & Me.ODBrand & "'")
That is similar to what I posted already here
...so give it a try and let me know...
0
 

Author Comment

by:thandel
Comment Utility
Right I would like to but I'm not sure hot to incorporate that into a report filter (At least I think it would be a report filter of some type.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
It is not a filter,...it simply "Finds" the value you say that you want.

This is why posting a sample database is always the best thing to do to help us get you a solution that works for your specific situation...
0
 

Author Comment

by:thandel
Comment Utility
OK I understand but how do I implement the DLookup with the report to "find" results based on the DLookup?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Thats just it, ...you shouldn't be doing this.
You should create a relationship between the two tables based on the common fields.
Then there is no need for Dlookup...
0
 

Author Comment

by:thandel
Comment Utility
OK now you lost me... you told me initially to use a dloookup then I asked how to implement in a report now you say I should use it.

"That is similar to what I posted already here
...so give it a try and let me know... "

so if I can how do I implement it?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I got confused when you posted a sample and there were no matched between the two tables
You also never actually posted a Report clearly showing the results you expectaed.

Can you post  a sample database that actually contains real representative data.
Then post a sample report showing exactly what you want the report to look like.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:thandel
Comment Utility
The sample DB does have real live data.. as far as what a report should look like I can format it just trying to find out how to "filter" it.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Filter it for what specifically?

In the actual report you posted, what *Exactly* do you want it to display?
Show me, *Graphically*, the  exact output you want.
In other words, ...don't say you want to filter or match, ...just show me the precise output you want.
0
 

Author Comment

by:thandel
Comment Utility
The report format isn't important... but you could reference the previously attached .MDB file. There is a report in that file.  If we can use that based on a possible filter of some sort that would do the trick.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Ok, I  see the report...
Now, simply take a screenshot of the report.

Then mark it up to show exactly what you are looking for...
0
 

Author Comment

by:thandel
Comment Utility
The format of the report is find... the data would be any order that is a year supply Qty.  So when for example ODQty (Tmain) =>  ShipMinQty (TCL)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Show me *graphically* what the report should look like.
0
 

Author Comment

by:thandel
Comment Utility
I've attached a sample of a format I've used in another DB that I would apply to this one once I have a solution to this.
0
 

Author Comment

by:thandel
Comment Utility
Attached now  :)
PrepBy.pdf
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
OK, I will investigate tomorrow
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
I think we are at an impasse here.
I fail to see any real similarity between the report in the database and the report you show in the PDF.
We can try this one last time.

*Based on the report in the database you posed*, what is the exact, graphical results you are seeking...

Please post an image of the exact report in your database and the exact data you need to see...
Please mark up this report.
JeffCoachman
0
 

Author Comment

by:thandel
Comment Utility
I'm sorry for the confusion.  I may have to rethink the desired output and question and repost.  I apologize for the wasted time.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
No need to apologize.
As you can see, I was willing to help.

I am willing to wait if you post the exact graphical results you need from the report in the sample db...
0
 

Author Comment

by:thandel
Comment Utility
Let me get my ducks in order, not sure when.  But I might be able to resolve myself if there is away to use dlookup filter a report.  If not possible this will have to wait until I can repost.
0
 

Author Comment

by:thandel
Comment Utility
OK I've attached an updated report... I open the report with a filter selection based on order original date entered and some other filter requirements from tMain.  I then populate lense/box and lens type from the table tCL using a dlookup in the report itself.

Lens/Box - =DLookUp("LensBox","tCl","CL = " & "'" & [ODBrand] & "'")
Lens Type - =DLookUp("Type","tCl","CL = " & "'" & [ODBrand] & "'")

All is OK but what is left to make this report useful is to only display orders that are a year supply or more.

There are basically 6 types of these products, daily, 2 week, monthly and yearly.  So using the lenses per box and the type (daily, 2 week etc) we can determine the number of boxes that are needed to list the order on the report as a yearly (or more) order.

Is this even possible to only show these orders (year or more supply) on the report?
0
 

Author Comment

by:thandel
Comment Utility
Here is the attachment of the report thus far... its including all orders based  on my initial open report criteria... once open I only want to show a yearly supplied order.
Sample-Report-2.pdf
0
 

Author Comment

by:thandel
Comment Utility
Perhaps some VBA code in the report details to only display when a year supply or greater?
0
 

Author Comment

by:thandel
Comment Utility
Forgot to add here is the existing report filter (working).... is it possible to add additional criteria based on a field value on the report?

sCriteria = "[tmain].[DateEnter] >= #" & dStartQtr & "# AND [tmain].[DateEnter] <= #" & dEndQtr & "# AND [tmain].Prepby = '" & FindEmployee & "' AND [tmain].[Hold] = 0 AND [tmain].[Trials] = 0"
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

10 Experts available now in Live!

Get 1:1 Help Now