473,320 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Troubles creating parameters for a report

My database is based on Work Order Numbers. If an employee enters labor, they must include the WO Number for it. Equipment, subcontractors, per diem and materials all must also have a WO number.

I have the following tables:
Work Orders
Labor (includes per diem and time entries)
Equipment
Subcontractors
Materials

I have created a summary report using the Materials table which lists the WO number, total labor, total equipment, total subc's, and total materials, if any, within a user-specified date range.

The totals of labor, per diem, equipment, subc's and materials use the DSum function.

The problem is that since I created the report based off of the Work Orders table, it lists all of the work orders, not just the ones which have transactions from any or all of the other tables. Is there some SQL or a query I can create to only show the work orders which have transactions from the other tables during the specified date range?
Apr 12 '07 #1
5 1459
Rabbit
12,516 Expert Mod 8TB
Inner join the other tables.
Apr 12 '07 #2
Inner join the other tables.
I'm sure this is something easy to do, but I can not figure it out. I tried it in a query where I included the WO, labor, equipment and materials tables. Zero records appear. I tried multiple queries with WO and labor, WO and equip, etc., which seemed to work but then I could not join THOSE queries.

What I want is to view a report which shows only those work order numbers that had activity for that time period. There may or may not be labor, there may or may not be materials, but if there is anything at all I want the WO to appear on the report with the corresponding information.

Could you give more detail on how to accomplish the inner join between the tables?
Thank you.
Apr 12 '07 #3
Rabbit
12,516 Expert Mod 8TB
If they don't have a record in each table then a join won't work. I would say make a query for each table and join on only one table and then union them but a union requires that they all have the same field names. Will that be a problem?
Apr 12 '07 #4
If they don't have a record in each table then a join won't work. I would say make a query for each table and join on only one table and then union them but a union requires that they all have the same field names. Will that be a problem?
They all have the field name "Work Order Number".

There are some other like fields such as date, ID, etc. but that's it.

How does a union query work?
Apr 12 '07 #5
Rabbit
12,516 Expert Mod 8TB
A union query takes the unique records from different tables. A union all allows repeats.

Expand|Select|Wrap|Line Numbers
  1. Table1
  2. LastName  SomeNum
  3. Doe       1
  4. Moe       2
  5. Schmoe    3
  6.  
  7. Table2
  8. LastName  SomeNum
  9. Doe       1
  10. Moe       4
  11.  
  12. Union Results
  13. LastName  SomeNum
  14. Doe       1
  15. Moe       2
  16. Schmoe    3
  17. Moe       4
  18.  
  19. Union All
  20. LastName  SomeNum
  21. Doe       1
  22. Moe       2
  23. Schmoe    3
  24. Doe       1
  25. Moe       4
Apr 12 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access...
8
by: Mark Flippin | last post by:
This is for a reporting problem using: Access 2000 SQL Server 2000 Both at SP3 I've a stored procedure in SQL Server 2000 which builds a result set from a disparate set of tables, utilizing...
10
by: Adis | last post by:
Asp.Net Visual Studio 2003 SQL Server. Hi, Obtaining Data Based Upon Multiple Selections From a ListBox... I have database in Sqlserver and ListBox (Multiple Selection Mode) in my Visual...
4
by: DeanL | last post by:
Hi Guys, I need some help creating a query that is going to take between 1 and 10 parameters. The parameters are entered on a form into text boxes that may have data or be empty. Is there a...
0
by: Mariana | last post by:
Hello, I am exporting reports in pdf format from .net 2005 . The export is working fine; however the user values of stored procedure parameters does not show in the report. What do I need to do in...
1
by: Rodo | last post by:
Hi all, I'm trying to generate a simple crystal report without a database. Several people mention the use of a dataset. Someone mention in a msdn forum that I could use the SetParameterValue to...
0
by: mmueller | last post by:
I am new to reporting services 2005 (reporting in Access for years and older versions of Reporting Services from time to time) and this is probably a dumb question... but I have no internal resources...
2
by: Andy | last post by:
Hi guys I having a problem creating a report in Access 2003 project talking to a SQL database through and ODBC connect. After hours of trying things from Access Help, MSDN and Google I still...
1
by: bstevenson | last post by:
Hi I am trying to open up a report from ASP and export it to PDF.I need to create dynamic report names depending on the parameters I send in the URL.I have the following link that I create but it...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.