Need Some Help With a WIP

[JobCoster]

I need some serious help with a a quasi-WIP report for the uppers. My problem is querying from different tables with different joins and making them one cohesive report.



Any help is greatly appreciated and I will give out specific details to interested parties.

VFP tables trying to create a report from Sage100 Data.

Thanks

-Josh

[orange]

Josh,
Welcome to the forum.
Not everyone here is intimately familiar with your environment, so it may be easier for readers if you described WIP and VFP in simple terms or examples. The more you tell us of your set up, the more focused the responses. Web or desktop based database?

[JobCoster]

I gotcha.

WIP=Work In Process
VFP=Visual FoxPro

Data connection is set and working fine FYI.

Desktop cost accounting software, Quickbooks on Sheet Rock Dust

There are 4 main tables that house the data I need. Whilst I can access the data and present it, I need a few more columns which I need help with.

Mostly these columns are pretty static calculations but they are hincky.

The whole goal of this is to present a working report that my uppers can look at that does not require me researching whether or not the number are correct and saving myself time in not having to collate multiple reports by hand.

[orange]

OK. So what exactly is/are the issue(s). Since this is an MS Access forums, I'm thinking you want to link to your VFP etc tables via ODBC and create a Report(or series of reports) in MS Access.
Can you tell us more about
a) the report
b) the tables
c) the tricky calculation?

[JobCoster]

Sorry to sound cryptic.

Main Tables:
actrec = Job Listing
jobcst = Job Costs
acrinv = Receivable Invoices
prmchg = Prime Change Directives

Join Properties:
actrec is the main table and is a one-to-many join for each table. I.e actrec.recnum is the unique ID which is linked to the other tables

So basically the job list in actrec is the primer for all other sums. One unique job for many costs, invoices, changes, etc.

I have queries set up that associate the actrec table with say the jobcst table to take every record from actrec and sum the cost field from the jobcst table. Same holds true for the other table associations. When I go to my final query to add calculations like gross margin % and such, I just cannot get them to show up on the query like. It would make much more sense if I showed what I have and what I'm attempting to get to.

[JobCoster]

What the report should look like, columns only.



What is in Access

[June7]

Don't know about orange but I still don't know what the specific issue is.

Yes, can build reports in Access. What you show with the image of Crystal Reports looks like using Grouping & Sorting features with aggregate calcs in footers. All can be done in Access. Also, subreports can often be a solution.