Query Trouble

[Boots6]

I'm trying to figure out a query to get an itemized detail list so I can see what materials I have at which job, but I'm having trouble figuring out the right way to do it. I have a Material Transfer Table with "JOB_IN" AND "JOB_OUT" fields. The Material Transfer ID is linked to the Detail Table that stores the Material, Qty, Price fields. I want to create a report that will show items by JOB#, but I can't figure out the query I need to create to get it to see what I want.

Any help or links are greatly appreciated. Thanks!

 

[PGAC]

Hi Carkick,


Shouldn't there be three tables? One for jobs, one for materials and one to link the two together?

The Materials table would contain all info on each unique type of material, the Job table would contain all info on each uniquie job and the third table would be a junction table that cross-references the other two as a many-to-many relationship.

 

[Boots6]

I tried that way, but I had trouble figuring out how to get it all to work. I don't have a main materials table like a normal inventory database would. I have 3 tables linked by their primary keys so that the user can't change or edit the data. All inventory data needs to be locked. The assignment was to have combo boxes that filter across on a sheet as the User chooses "Main Category", "Subcategory" and "Description" properties. It's construction stuff so there are main categories like Lumber, then Subcategories, then Lengths, Widths, etc to choose from. I couldn't figure out how to do this with it all in one table and have it filter the way they wanted.

My problem with Projects was I didn't know how I would link it because the form I'm creating for the user needed to have "Project_In" and "Project_Out" fields both referencing the same table on the form. The materials don't go back and forth between all projects so in my reports I need to show how much went in, how much came out, and what the delta is for shrinkage.

Did I go about this the wrong way? I was having a really hard time going about it how Access would dictate because my bosses want it so specialized. I appreciate the feedback as I learn here

 

[kjv1611]

Well, on your very table structure, from what you're saying the needs are, it'd be easier to work within one table than you think:

1. Table fields:
MaterialID
Material (lumber, steel, etc = values)
Length
Width
Height
Weight
Color

2. To query fields form your combo box... you'd basically build the where clause for a recordsource query based on the input from each field... you can do this with VBA. So.. you'd have it do things like:
-if user chooses a Material by name, you'd ad a where clause for that...
-if user specifies a specific length or length range, you'd put that in a where clause..
etc...

One way to accomplish this dynamic query setup to where you can add/remove different where clause pieces would be to have another table that stores the current filter details (where clause lines)... so for each request, you'd add a new record.. at the end, clear them all out. When you build the query in VBA, you'd look to the first record (use an ID field to determine which is first or last), you'd loop through the items in that table, and build your where clause accordingly....

Is that making any sense - the general idea?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Boots6]

It does make sense thank you, but I think I'll leave my materials how they are since it's working now. I'm just trying to figure out how to get my report to calculate my In and Out properly.

I have a Material Transfer table that has Job_In and Job_Out categories. Whenever a material is leaving one job#, it is going to another. So my Job In field could be Job# 8 and my Job out could be Job# 20. The Material Transfer table is connected to the Detail table which lists my materials through the Material Transfer ID.

My report is sectioned by Job Number. I need my report to look at the "Job_in" field and search the "Job_Out" query for any materials going out of that same job that match the materials that went in so it can show the Delta. I tried a nested Iif with DLookup, but my queries keep doubling up the figures. Is there an easier way to do this?

Thanks!

 

[dhookom]

Carkick,
Is there any way you could provide enough records with actual table and field names so we could see what you are talking about and what you need? You mentioned field "Job_In and Job_out" then you later state "Project_In and Project_Out". What do you mean? Sample records and desired output might clear up my confusion.

Duane
Hook'D on Access
MS Access MVP

 

[Boots6]

I want to attach pictures of my forms/reports but I'm not sure how. Sorry about the confusion, it is ONLY JOB_IN and JOB_OUT.

My Material Transfer Table has the fields:

JOB_IN
JOB_OUT
DATE_MT
etc...

My Detail Table has fields:

MAIN_CATEGORY
SUB_CATEGORY
DESCRIPTION_CATEGORY
QUANTITY
MATERIAL_TRANSFER_ID (linking it to the material transfer)

My JOB_IN and JOB_OUT Queries are the same with the obvious exception of whether the materials were coming in or going out. They have the fields:

JOB_IN (or OUT)
D_ID (Item Description ID - this serves as the inventory ID)
Sum of QTY IN (or OUT)

My JOB TOTALS Report Needs to show the following things by JOB #. So it has the JOB# as the header and these categories under:

MAIN_CATEGORY
SUB_CATEGORY
DESCRIPTION_CATEGORY
SUM OF QUANTITY IN
SUM OF QUANTITY OUT
DELTA

I have all of it but the SUM OF QUANTITY OUT and DELTA. I need the report to look up the JOB_OUT QUERY by JOB # and DESCRIPTION CATEGORY to provide the total number of items that came out of that same job so we know if that job lost materials or returned them all.

So an example would be I have 750 hammers going from JOB #999 to JOB #111. At the end of the project I have a material transfer of 600 Hammers from JOB #111 TO JOB #888. I want my report to Show JOB #111. Under it, it would show Hammers (and all other pertinent materials), Total In = 750. Total Out = 600. Delta = 150

It sounds simple, but I'm not quite getting it.

Thanks for any help!!

 

[dhookom]

There is no "MATERIAL_TRANSFER_ID" in the material transfer. Are you storing a quantity in the in and out fields? If so, I would have had a single quantity field and a status field that identified material direction (in or out). You report would just group by the Job Number. The number IN would be:

=Sum(Abs([InOrOut]="IN") * TransferQuantity)

The number OUT would be:

=Sum(Abs([InOrOut]="OUT") * TransferQuantity)

The difference would be:

=Sum(Abs([InOrOut]="IN") * TransferQuantity) - Sum(Abs([InOrOut]="OUT") * TransferQuantity)

Duane
Hook'D on Access
MS Access MVP

 

[Boots6]

Sorry, there IS a Material Transfer ID and a Detail ID.

 

[Boots6]

I think I was close using a DLookup with:

=DLookUp("[Sum Of QTY]","qryJOB_OUT","[JOB_OUT] = [Reports]![rptJOBTOTALS]![JOB_IN2]" And "[D_ID] = [Reports]![rptJOBTOTALS]![D_ID]")

Don't mind my labeling of JOB_IN2 - it was necessary.

Thanks!

 

[dhookom]

If you use my suggestion, you can create a crosstab query with the InOrOut field as the column heading.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

DLookup() in reports is typically a poor solution.

Can you simply provide the fields in your report's record source as well as some records?

Duane
Hook'D on Access
MS Access MVP

 

[Boots6]

Fields in Report:

JOB NUMBER (this is the header for each section)
MAIN CATEGORY
SUBCATEGORY
DESCRIPTION
TOTAL IN
TOTAL OUT
DELTA

That's it. So my first section says 997,

It has records:

Hardware, Safety, Steel Bracket, 794 (Total in), 0 (toatl out), 0 (detlta)
Hardware, Safety, Tensioner, 10 (total in), 0 (total out), 0 (delta)
Plywood, Particle Board, 5/8" 900 (total in), 0 (total out), 0 (delta)

It's all working how I want except the Total Out and Delta fields won't populate correctly.

 

[dhookom]

If [TOTAL OUT] is numeric then you should be able to create a total in a group or report header or footer section with:

=Sum([TOTAL OUT])

The Delta should be:

=Sum([TOTAL IN])-Sum([TOTAL OUT])

Duane
Hook'D on Access
MS Access MVP