Start Free Trial

asked on

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

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.

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

Jeffrey Coachman

"MinorderQty", ...by what?
By customer?, by Order?, by year?, by Product?

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

ASKER

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)

Jeffrey Coachman

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?

ASKER

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

Jeffrey Coachman

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

ASKER

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.

Jeffrey Coachman

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

ASKER

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.

ASKER

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???

Jeffrey Coachman

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

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

ASKER

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.

Jeffrey Coachman

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...

ASKER

OK I understand but how do I implement the DLookup with the report to "find" results based on the DLookup?

Jeffrey Coachman

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...

ASKER

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?

Jeffrey Coachman

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.

ASKER

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.

Jeffrey Coachman

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.

ASKER

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.

Jeffrey Coachman

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...

ASKER

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)

Jeffrey Coachman

Show me *graphically* what the report should look like.

ASKER

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.

ASKER

Attached now :)
PrepBy.pdf

Jeffrey Coachman

OK, I will investigate tomorrow

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I'm sorry for the confusion. I may have to rethink the desired output and question and repost. I apologize for the wasted time.

Jeffrey Coachman

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...

ASKER

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.

ASKER

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?

ASKER

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

ASKER

Perhaps some VBA code in the report details to only display when a year supply or greater?

ASKER

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"