Append filter data from multiple tables to one table

Ok I have 3 tables that I need to pull data from and put the results into 1 table.  The part I am stuck on is the filtering part.  In the query, Where should be the current month from tblworkform date field.  Any help would be greatly appreciated!  This question is kinda tough to word so if more information is needed, please let me know.  Thanks in advance!

INSERT INTO tblmonthlyreport ( wkdate, wkname, wktotalhrs, wkstorenumber, opdatepurchased, opname, opstorenumber, ptneeded, ptdateordered, ptstorenumber )
SELECT tblworkform.Date, tblworkform.Name, tblworkform.[Total Hrs], tblworkform.[Store Number], tblops.[Date Purchased], tblops.[Op Name], tblops.[store number], tblepartsform.[Parts Needed], tblepartsform.[Date Ordered], tblepartsform.[store number]
FROM tblworkform, tblops, tblepartsform
WHERE (((DatePart("M",[tblworkform.date]))=DatePart("M",Date())));
LVL 2
russell12Asked:
Who is Participating?
 
lwadwellCommented:
The problem you are now facing is that you have not 'joined' the tables together via any relationships in you query.
Define the relationship then double click the relationship line (or right click and select 'join properties') ... you need to define the query as an 'outer join' and select either option 2 or 3 ... I think, based on the data I saw in you sample that you want the "Include ALL records from 'tblworkform'..." option.
0
 
als315Commented:
Upload DB with these tables, query and expected result.
If you have more then one year in your tables, you should also filter year. You have 3 tables, but I don't see any joins in query. Sample database is needed.
0
 
lwadwellCommented:
You have specified 3 tables in the FROM but have not specified any join criteria between the tables ... is this right?

>> Where should be the current month from tblworkform date field.
I am afraid I am not sure I understand the question ... you have in the WHERE clause a function to extract the month from the tblworkform date field - is that what you mean?  The syntax should be: DatePart("M",tblworkform.[date])
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
russell12Author Commented:
Ok the expected results is all three tables go into 1 table.  For example, tblworkform, tblops, tbleparts, go into tblmonthly reports.  So it will take all three tables per row and put it into 1.  I have uploaded the db.  I have the query the way it needs to be the only problem is, it is not pulling from the tblworkform [date].  I am sorry if I am still confusing you all, it is just very hard to explain what I want done instead of just showing.  Thanks!
workorder.zip
0
 
als315Commented:
Access is relational DB and you had to have relations between tables. I don't understand relations between your tables. Item is primary key in 2 tables. Is it same Item or not? Can you create relations between your tables in DatabaseTools - Relationships?

Here you can find wonderful rules about sample DBs.
0
 
lwadwellCommented:
What makes you think that it is not pulling the tblworkform [date]?  You say that it is not ... but I do not understand what you mean by that?  Is it not including it in the output?
Having the db helps in giving something to refer to - it does not explain the problem nor does it show us what you want.

All I get so far is:
- you have three tables in a query
- these tables are not related to each other as there are no joins between them
- you want certain columns extracted from these tables into another table.

But:
- it specifies two output columns that are not defined in any of the tables, tblops.[store number] & tblepartsform.[store number]
- you only want rows from the tblworkform table for a particular month .. extracted from an unknown function "Date()" ... did you mean the current date, i.e. Now()
- tblworkform, in the file you uploaded, only contains data for April anyway - so no data is extracted.

Are any of these your problems?  If so - which one?  If not ... please describe what is not doing and what you would like it to do.
0
 
russell12Author Commented:
Ok when it comes down to relationalship db, I have never gotten it.  I have been programming with Access for 3 years now and I have attempted multiple times to do a relationship db and can not achieve it.  Now with that being said, I thought that was one of the things I was going to have to do.  Also with the sample db I uploaded, it actually has all its data removed except for dates.  We are using the db at this time, just trying to make some improvements.  So when I was trying to run this query, I had 20+ august entrys.  It would still not work.  I kept getting a function error.  Ok to answer lwadwells questions, The tblepartsform.[store number] and tblops.[store number] is actually on the sql server db.  I used the local tables that were already in the db itself.  It was not updated and these 2 fields were added after i upsized the db.  So they are there just not on the local db.  Also I do not want now() because all I want to extract is data from this month.  Example if I was running reports for this month, It would only show results for (8) in the date field of tblworkform.  I hope this helps, and again thanks for being patient with me as it is very hard to explain and not show.  Thanks again!!!
0
 
lwadwellCommented:
But you do want Now() ... the where clause should have been:

  WHERE (((DatePart("M",[tblworkform.date]))=DatePart("M",Now())));

The function Now() gives you the current date.
The function DatePart("M",<value>) extracts the month from the value ... to do a month = month comparison.
0
 
russell12Author Commented:
Ok when I put that in the query, It is duplicating records.  For example if item 1 didnt have any ops or parts, it makes 18 duplicate entrys of all information in tblworkform for ops and parts.  Any idea why?  I think this is because the realationships isnt there.  But when I attempted to define relationships, It will not show the entrys that do not have any ops or parts on them.
0
 
russell12Author Commented:
Thank you both!  Lwadwell, Thanks very much, this is the second problem you have answered for me and if I remember correctly, the other one was a train wreck also, so thanks for bearing with me and helping me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.