[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 646
  • Last Modified:

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())));
0
russell12
Asked:
russell12
  • 4
  • 4
  • 2
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now