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",[tblworkfo
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",[tblworkfo
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.[
>> 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.[
ASKER
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
workorder.zip
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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!!!
But you do want Now() ... the where clause should have been:
WHERE (((DatePart("M",[tblworkfo
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.
WHERE (((DatePart("M",[tblworkfo
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.