Table Setup Help...

[AC5FF]

Paul
I'm going to go try this, but I want to make sure i understand correctly. (EDIT: didn't understand LOL)

I should add a new field to my Enterprise Collaboration table called "ID"
Did you make this a Primary Key?
Did you enter data into this field based of the LRU table?

I wish i could 'see' the query vs the SQL view. I still have trouble "reading" these. Did you 'write' this or use the graphical interface?

Can you attach the sampledb again w/your changes?

Even copying your SQL code and putting it right next to mine I get lost before the first "sum" statement... Not sure why yours would be that different from mine..... I really AM Access Illiterate! LOL But I'm learning still!

 

[pbaldy]

Sure, here's the db back. I added the field because I felt the ID field should be the one stored in other tables, plus the type mismatch between dissimilar fields screwed up what I was trying to do. It's sort of like if you had a table with employee ID and name, you'd always use the ID field in other tables, not the name field. Whether you choose to use it going forward is up to you; I did populate it with the appropriate values from the other table (I think).

I created the query in SQL view because the join between those 2 tables is an unusual one that can't be represented in design view. You could certainly build most of the query in design view and then add that join when everything else was done. You could also get the quota with a DLookup, but I think the join will prove more efficient if the amount of data is large. I copied the formulas from your query, though I dropped the date criteria from one, as the query already restricts to the same date range (unless I misread it).

By the way, are you in the Air Force?

 

[AC5FF]

Paul;

This looks like it is working; but I'm just having a hard time understanding/reading the SQL. My learning curve there. I am looking at just a few differences compairing your SQL to what I have... that nz(max(quota),0) through me for a bit; had to pull up the o'l help feature to learn what "NZ" meant..

I'm goign to try and incorporate this into the real DB; but that LRUID field is going to hurt. I could probably write a quick append/? query to auto-fill in the data for what i have already; but is there a way with the lookup feature to auto-fill this field when I enter the LRU data? I tried to do that working off your last post, but it didn't work for me... I could just be approaching things wrong.

BTW: Yeah, AF here.. How'd ya guess

EDIT: Thanks; this works just as required!! I wrote a quick append query to load in all the LRUID values in the Enterprise table. When I ran your query everything works.... except... Always an "except" isn't there?? HAHA
I'm going to see if I can figure this one out though... Had a problem with sorting, but I think i figured that one out... I also have a problem running the report for this; but I think I know where to look to do that too. It's a HUGE learning curve for me working with the SQL vs the drag n drop interface. But you gotta learn sometimes

I'll let you know if I can get it working... In the meantime; you said I should be able to write this with the GUI vs SQL and just edit the join properties later?? (just in case I cant figure the SQL out LOL)

Awsome help Paul... Thanks a million!

 

[pbaldy]

You could do anything that didn't include the quota table in the GUI. Because the join to that table isn't a simple one, it can't be represented in the GUI (which you probably found out if you tried to view that query in the GUI). Let me know if you get stuck on anything.

My daughter is in the AF, recently returned from deployment (maintenance officer). Her husband is over there now (pilot).

 

[AC5FF]

Prayers are w/the husband! Definatly not a fun time! Where's the daughter stationed?

Yeah; I got the error trying to view in design view! LoL
I had to re-do my report, but things are displaying as required.. Awsome Deal
I can't get the 'sort' function working though... When I re-wrote the report I told it to sort by the "contracted" number, decending order... It doesn't do that though when I run the report... So I tried to edit the SQL query. I added:

ORDER BY [Enterprise Repair Collaboration].quota DESC;

right after the last line in the SQL. But when I try to exit or run the query I get an error "Charicters found after end of SQL statement". If I try to put the ORDER BY line above the last line (GROUP BY) I get an error saying "The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect."

I'm at a loss as to the punctuation/reserved word ... I would have thought this would be an easy fix....

 

[pbaldy]

It would go at the end, but make sure you take the semi-colon out from the end of the GROUP BY clause. The semi-colon ends the SQL statement (and is not actually required in Access, but the GUI puts it in).

They are both stationed in NC. They're at the same base but with different squadrons, so they went one after the other. They had a 2 week overlap over there, so they got to spend a little time together. Where are you stationed?

 

[AC5FF]

Hmmm
This one is going to take a while to figure out... I found another error somewhere, just not sure where the problem is...

I am working in my original DB, so i might have to go in and try to reproduce the error in the sampledb. I took an item from the LRU table that is flagged in the "Enterprise" field and added it to the Enterprise Repair Collaboration table for March. When I re-run the query that item is not on the list. It isn't on the list at all to begin with either. The LRU table has 37 items flagged, but only 20 display on the query.

Definatly better than just the ones w/quota's that I started with..

Would there be a reason the query would limit to only 20? It's not like it pulls the first 20 in the LRU table either, it's almost random...

Hmmm:

 

[AC5FF]

I ... almost ... got stationed in NC years ago. As much as I would have liked it there, I don't think i would have enjoyed my job as much. I'm in SD currently (at leat for another year.. )

 

[pbaldy]

There's no reason it should limit to 20 records, no. If you can update the sample, it would certainly help sort it out.

That's probably the only area of the country I haven't been to (the states along/near the northern border). My boss was up there last summer and loved it. What do you do, if you don't mind my asking?

 

[AC5FF]

I was beat by the last msg; so tomorrow is going to be another fun day.
Updating Tables and Filling Cavities! LOL Hate the dentist

My AF Job.. Tough one to answer... What's my "JOB" or what do I do? LoL
I'm actualy an avionics technician by trade... Work'd supply jobs probably 10 out of the last 12 yrs Can't complain though; I've loved EVERY day. And its jobs like maintaining and updating this Access DB that keeps the job interesting. I knew nothing about Access less than 9 months ago; now I'm asking for lots of help, but building all sorts of stuff in it... LOVE it..

 

[AC5FF]

Think I got this working now... Yayyyy I figured something out on my own! LOL

One of the "Where" statements in the query was looking at all the TIN Dates between X and X. I needed that where/between statement on fixed units, but not as an overall conditional format. This was my mistake to start btw.. I believe I had that in the original sample I sent out...

Anyhow; removing that conditional format brought everything back and displaying how it should. I appear to have a small hickup on one or two of the sums or quota information, something didn't appear right when I was looking at it; that's just going to take a little more digging to discover.

Again Paul; thanks for all this help! With one or two more 'tweaks' this thing will be a completed project I can nock out of my 'to-do' list..

 

[pbaldy]

Happy to help. Post again if you get stuck.

 

[AC5FF]

Stuck .... LOL
I think i'm pretty sure I've figured out what is going wrong, just not how to fix it. Any time I try to edit the SQL I get errors (my format or my ignorance w/SQL)...
Because I had to change a few things around; here is what the SQL is currently:

SELECT Work.[LRU ID] AS Abbr, Sum(IIf(work!status=4 And work!code=11 And work.[tin date] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0),1,0)) AS Repaired, nz(Max([Quota]),0) AS Contracted, ([Repaired]-[Contracted]) AS OverShort, Sum(IIf(work!status=1,1,0)) AS AWM, Sum(IIf(work!status=2,1,0)) AS AWP, Sum(IIf(work!status=3,1,0)) AS DWP
FROM ([Work] LEFT JOIN [Enterprise Repair Collaboration] ON (Work.[LRU ID]=[Enterprise Repair Collaboration].LRUID) AND (DateSerial(Year(Work.[TIN Date]),Month(Work.[TIN Date]),1)=[Enterprise Repair Collaboration].Month)) LEFT JOIN lru ON lru.ID=Work.[LRU ID]
WHERE lru.Enterprise=True
GROUP BY Work.[LRU ID];

The problem is in "nz(Max([Quota]),0) AS Contracted" .. Because I took the "WHERE" statement out that limited [work].[TIN Date] to the current months data it also seemed to effect what data it pulled from the Enterprise table.
Is it possible to write that NZ statement w/a IIF date function? I've tried a couple ways, but keep coming up with format errors.....

I may be wrong; but now i'm not sure where/how it's getting the quota numbers. Some match Feb, some Jan, some Dec numbers; then there are ones that don't match any... Very wierd.

In the above query, From section; can you walk me through the part after the "AND" ... That's where I am getting lost and it's probably where you mentioned the join got 'wierd'

 

[jdraw]

Stuck .... LOL
I think i'm pretty sure I've figured out what is going wrong, just not how to fix it. Any time I try to edit the SQL I get errors (my format or my ignorance w/SQL)...
Because I had to change a few things around; here is what the SQL is currently:


The problem is in "nz(Max([Quota]),0) AS Contracted" .. Because I took the "WHERE" statement out that limited [work].[TIN Date] to the current months data it also seemed to effect what data it pulled from the Enterprise table.
Is it possible to write that NZ statement w/a IIF date function? I've tried a couple ways, but keep coming up with format errors.....

I may be wrong; but now i'm not sure where/how it's getting the quota numbers. Some match Feb, some Jan, some Dec numbers; then there are ones that don't match any... Very wierd.

In the above query, From section; can you walk me through the part after the "AND" ... That's where I am getting lost and it's probably where you mentioned the join got 'wierd'

This may be useful to understanding SQL

http://msdn2.microsoft.com/en-us/library/aa140015.aspx

 

[AC5FF]

Ya know; when I got this job and found out i was going to be maintaining an Access DB I went out and bought two SQL books (Okay, one was SQL for Dummies! LoL) and the Access QUE bible .
The QUE book has come in handy a couple of times, but I get totally lost when I try to find/learn from the darn SQL books. I just need to take myself a course on SQL!
Goin to that website now to see what I can find

 

[AC5FF]

I tried that website... Lost already But I'm working at it....

This morning I thought to myself that there has to be another way around all this difficulty. And I may have come up with an idea, but it isn't working as simple as I had hoped. Thought I would run this by you and see what you thought...

Instead of running a query to get the data i need here (it's pretty much thrown straight to a report) could I instead throw the data into a table?

What got me thinking along these lines is that I download supply data daily. This is imported into the database into a table every day. The macro that runs this clears the table, takes the new supply report and dumps it in. Basically I'd like to do the same.

I already have the query working to pull out just the current months data from our work table. I use that query to source an append query to the new table. Then, using an update query I update the quota column in the new table from the Enterprise Collaboration table.

FIrst part worked just as i wanted it to. The new table filled with everything it needed, but the update from Enterprise Collaboration isn't working as I'd expected.. I probalby just need to fiddle with it a little more. But since I was taking a break from doing that I thought I'd see if I could get an opinion on if this woudl work or not.

 

[AC5FF]

Done Finished Next Project! LoL

Building the table w/a delete/append/update query has got all my data sorted/displayed just as I need. Reports built and it runs like a champ.

May not be the 'best' way to go about things, but, if it works.. why complain