Problem combining two tables, need help with Query

[bri822]

I am having a problem printing out a report. I need to show the quantity and other information for jobs seperated by day. The only thing is, I have to take the quantity off of two seperate tables.

Table 1 - some jobs take more than one day, so it contains the spread of quantity for a job. Therefore the same job mails on more than one day.

Table 2 - has all jobs, including the total for the jobs that mail on multiple days. It also contains needed information about status of the job and some other information.

Pretty much, I need to combine the information in table 2 with the quanitities and days of table 1. I can't figure out how to go about doing this. But I think I need to some how copy table 1 with some extra information fields that I need for table 2. Then go back in and update the information in this third table if the jobs are the same. And then append the jobs that are one day jobs on the end of the table/query.

I am not sure if anyone can really make sense of this, but I am really stumped. I would really appreciate any information that anyone would have. If I did not explain it well enough, please email me at bri822@yahoo.com and I would be happy to explain it more. Thank you!

Brian

 

[RV]

The first step I suggest is to normalize your database structure.
1) your Table 2 contains data which can be derived from your Table 1, so you don't need (and shouldn't) use these table
2) Reading your post I'm cetrain your Table 1 should be split up in separate tables (Table 1 seems to be not properlynormalized)

3) in relational databases (like Access is considered to be..) it's good practice to extract data from tables using queries...

Don't create extra tables to gather derived data from other tables.

I advice to have a look a the principles of normalization, there's some good stuff here to be found....

You can also post your database and let us help you on your way through the world of Access....

RV

 

[Jon K]

Yes, we need more information. Post the fields and some records of your tables and also the format of your requirement.

 

[bri822]

RV and Jon K,

Thank you for taking your time to help me out. About the tables structure. The reason I am using both tables, is because the number is jobs that mails on multiple days is few in relationship to the total number of jobs. Of the 1,000 jobs a year, maybe 40 are multiple day jobs. To make one table, I would have alot of repeat information (there are atleast 15 other fields that would be repeated for each record which are identical, if I were to make a seperate entry by the mailing date) In an effort to conserve space, I made them into seperate tables. I admit I have not been using access too long, but I have been programming for a few years, and I just felt it a waste of space to have repeatitive data. If I am still wrong, by all means, please let me know.

As for field names, Table 1 has Job number, Qunatity, and Mailing Date. Table two have Job number, Quantity, Mailing Date, and status. I simplified the second table to contain just one extra field in the example, because I figured if you can solve the problem for one extra field, I can relpicate the solution to include other fields. Just for your information though, table 2 has over 15 other fields.

Here is an example of a job that mails 3000 over a 3 day period

Table 1:
Job Num Date Quantity
1 6/24/02 1000
1 6/25/02 1000
1 6/26/02 1000

Table 2:
Job Num Date Quantity Status
1 6/26/02 3000 Active

Need the report to show in report:
Date Job Num Quantity Status
6/24/02 1 1000 Active
6/25/02 1 1000 Active
6/26/02 1 1000 Active

If anyone is confused still, please let me know and I will try to explain it better. Also, table two and the end report need to show about 15 other fields, otherwise I would have combined the two tables, I just used Status to simplify the explaination of the problem.

 

[Jon K]

I am not sure if I understand your problem fully. But if what you want is to join Table 1 and Table 2 so that the records will be displayed as you have shown for the report, you can join tables 1 and 2 with a SELECT query:

SELECT a.[Date], a.[Job Num], a.[Quantity], b.[Status]
FROM [Table 1] as a INNER JOIN [Table 2] as b on a.[Job Num]=b.[Job Num]
ORDER BY a.[Job Num], a.[Date]


To create the query (I use Access 97), click New in the Queries tab. Click OK on Design View. Click Close on Show Table. From Access' View menu, click SQL View. Copy and paste the above SELECT query to the SQL View window. Replace Table 1 and Table 2 with the correct table names. Click on the Run button (the one with the red exclamation mark on it) to run the query.

If the query runs successfully, you can add the other fields from table 2 at the end of the select clause, separating each field with a comma and preceding each with b. When you exit the query window, save the query as qryCombined.

You can then create your report from qryCombined as if it was an Access table.

Hope this helps.