By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Compiling a history using queries and tables

P: 8
Hello Everyone again,

I have hit a brick wall and was wondering if someone could shed some light on my problem.

So what I am trying to do is to create a table that stores a weekly snapshot of a table called [Quals], and then produce a report that shows the current Qualification status as well as the last three Qual counts.

I have constructed a macro that uses Runsql commands to produce a table called [Qual History] and everything seems fine there.

But my breakdown is when I try to get all the information I want / need in a report I get duplicate entries for every person depending on how many times I append to the table [Quals History].

I understand that there is duplicate information in the table and thats ok but when I goto do the report I need it to list the following information:

[Personell].[Rank], [Personell].[Lastname], [Quals].[Track], [Quals].[Points Required], [Quals].[Current Points], [Quals].[Dink], and then basicly pull 3 entries from [Quals History].[Points Required], [Quals History].[Current Points], [Quals History].[Dink], [Quals History].[Date].

I have generated a SQL statement to try and do this but its rather lengthy but I'll post it for further analysis. Please don't read to much into the SQL statement posted because it just may be all together wrong. I get all the data stored in the tables correctly the way I want but I need it to display on the report correctly is all.

Thanks for everyone's help with past and current issue's,

TheChazm

Expand|Select|Wrap|Line Numbers
  1. SELECT Personell.Rank, Personell.[Last Name], Quals.Track AS Quals_Track, 
  2. Quals.[End Month], Quals.Quals AS Quals_Quals, Quals.[Points Required] AS [Quals_Points Required], 
  3. Quals.Qualified AS Quals_Qualified, Quals.[Current Points], QualsHistory.Track AS QualsHistory_Track, 
  4. QualsHistory.[Points Required] AS [QualsHistory_Points Required], QualsHistory.Qualified AS QualsHistory_Qualified, 
  5. QualsHistory.Dink AS QualsHistory.Dink, QualsHistory.Date, Quals.Dink AS Quals_Dink, 
  6. QualsHistory.Quals AS QualsHistory_Quals, QualsHistory.[Current Points] 
  7. FROM (Personell INNER JOIN QualsHistory ON Personell.KEY = QualsHistory.ID) 
  8. INNER JOIN Quals ON Personell.KEY = Quals.ID 
  9. WHERE (((Quals.Track)=Yes) AND ((Quals.Qualified)=No) AND ((QualsHistory.Quals)=[Quals]![Quals])) 
  10. ORDER BY Personell.[Last Name];
Nov 29 '08 #1
Share this Question
Share on Google+
3 Replies


P: 8
I'm currently thinking of redesigning the tables themselves and running 3 queries to build a table souly for the report. I dunno this is getting pretty complicated. Any help is greatly appreciated.

Thanks,

TheChazm
Dec 1 '08 #2

Expert 100+
P: 1,287
Can you post the fields in your tables and how they are supposed to relate to each other?
Dec 1 '08 #3

P: 8
I appreciate the reply but I think that if I just posted the table structures and the data associated with I would have to fill up the entire screen. Again I appreciate the help but I'm just going to keep trying. I'll figure it out eventually anyway.

TheChazm
Dec 3 '08 #4

Post your reply

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