I've approached this once before but I never did figure out how to go about setting up this table. If anyone can steer me in the right direction I'd appriciate it!

Overview: I have 25 products that I track repairs on. I am given quotas every month for some or all of those products. All this data needs to be kept vs overrighting last months quota information with this month's.

What I have done was created a table with columns of: ProductID, 1/08, 2/08, 3/08, 4/08, etc... and ran that out for a years time. So, that is how my table is currently formatted...

Now when I need to pull out information I have a query that looks at a particular month column and compairs that to my work table. However, every month I need to go into that query and change the column name it looks at in my quota table. This again works, but is it the best way? I also need to pull out history information month by month for a production graph. The query I had to build again requires me to manually go in and enter new month information every time it's needed.

It just seems to me that there is probably a better way to format this quota table. If I remember correctly someone suggested to me that I needed to normalize this table; and the format suggested was more like:
productID; date; quota
But instead of just having 25 lines I will now have 25 each month. That just seems like it'll get excessive real quick...

SO; if there are any great ideas for setting up this quota table vs what I am currently using I would really like to know!

Thanks!!

I've approached this once before but I never did figure out how to go about setting up this table. If anyone can steer me in the right direction I'd appriciate it!

Overview: I have 25 products that I track repairs on. I am given quotas every month for some or all of those products. All this data needs to be kept vs overrighting last months quota information with this month's.

What I have done was created a table with columns of: ProductID, 1/08, 2/08, 3/08, 4/08, etc... and ran that out for a years time. So, that is how my table is currently formatted...

Now when I need to pull out information I have a query that looks at a particular month column and compairs that to my work table. However, every month I need to go into that query and change the column name it looks at in my quota table. This again works, but is it the best way? I also need to pull out history information month by month for a production graph. The query I had to build again requires me to manually go in and enter new month information every time it's needed.

It just seems to me that there is probably a better way to format this quota table. If I remember correctly someone suggested to me that I needed to normalize this table; and the format suggested was more like:
productID; date; quota
But instead of just having 25 lines I will now have 25 each month. That just seems like it'll get excessive real quick...

SO; if there are any great ideas for setting up this quota table vs what I am currently using I would really like to know!

Thanks!!

Here 's a link that should help.
It deals with Table design and rationale.

http://r937.com/relational.html

I agree with whoever told you to normalize the table. It will be a nightmare to maintain in its current format.

Jdraw:
I think I've looked at this page before; but... Just skimming it again I was looking at the one to many relationship.. That might work, but again I keep seeing a table that increases by 25 lines each month...

Paul:
That's what i"m hoping to accomplish... sort of normalize this table before it does get out of control.


Would this idea of one-many relationships, or more to the point, adding 25 lines to my quota table each moth, be considered 'normalized?

Jdraw:
I think I've looked at this page before; but... Just skimming it again I was looking at the one to many relationship.. That might work, but again I keep seeing a table that increases by 25 lines each month...

Paul:
That's what i"m hoping to accomplish... sort of normalize this table before it does get out of control.


Would this idea of one-many relationships, or more to the point, adding 25 lines to my quota table each moth, be considered 'normalized?

What tables do you have?
What are the columns/fields in those tables?

Would this idea of one-many relationships, or more to the point, adding 25 lines to my quota table each moth, be considered 'normalized?

Generally yes. An old saying is spreadsheets are short and wide (lots of columns, fewer rows) and relational databases are long and narrow (more rows, few columns).

Pbaldy; I've never heard that saying... but in this case it makes sense. In my current format I fall more under the 'spreadsheet' look vs the relational database.
I'll have to play around a little to see what I can get working :)

JDraw;
tables: Quota, work and Product tables.
Quota is described above; Work lists date received, date completed, repair action(code), and a lot of other data used elsewhere; the product table lists all the info on the products; Part#'s, nouns, common names, costs, etc...

In the meantime I'm going to attempt to re-accomplish my quota table... 3 columns; productID, Month, Quota. See what sort of road this takes me down :)

But I put myself in a delemma :) Since I set up the original quota table I've had to work with that format. If I change it... LOL... I'm back to re-accomplishing all the queries/reports/graphs/etc... LOL... Good news is that it SHOULD be easier the 2nd time around.. :)

Do you need to keep a history of quotas? maybe a lookup table of the current quota may help

Yes, defiantly need the history...

I will need to go back through and pull histoy data at least monthly (seems like weekly though! LOL). Right now I have to graph so many different views/products/etc.. it's crazy. Mgmt just wants TOO much information at times! LoL

Other than my graph; this might be easier than I thought!! WOW.. Thanks Everyone!!

Let me ask a question that might be better posted in the 'Query' forum, but since it relates to what I am doing.....

My new quota table is set up: Product ID, Month, Quota
I added a field to my product table; Yes/No, to identify all products that i 'could' have a quota on (this was 28 products out of 215). Now, for the month of March I have a quota on only 6 products.

My query, that lists quota, repairs, #units waiting repair, #units waiting parts all works.
The problem is it only lists the numbers where there is a quota, it does not list the all the products flagged. I can understand why... In the query the quota table is tied to the product table, which is tied to the work table. it's only going to list those items that match through all 3 tables... In this case the shortest list would be the quota table. ..... .....
Where I need to get this is to list all 28 products, quota, etc..


Any ideas?

It might help to post a sample db, but generally a LEFT JOIN in the query instead of the default INNER JOIN. The first query would be the one with all the available options. In design view, you'd edit the join and choose "Include all records...".

Paul;
If I am following correctly.. Left Join/Inner Join. I've not dealt with that terminology.. :)

If I double click the join line I get the properties window. Left table is Quota right table is Product. The bottom gives options 1, 2 and 3. I've tried all three options. Option 1 only gives the list of products w/quotas. Option two gives 5 lines for every product, I.E. March has 5 products w/quotas. All the products will be listed w/product 1's quota, then all listed w/product 2's quota etc... Then option 3 returns a SQL error.

Does it matter what table is right or left??? Never looked at it that way. Just dropped back in and gave that a shot.. didn't work.

Here's an idea; not sure if it'll be helpful or not. Below is a paste of the SQL view of this query. I apologize for the difference in field names/etc..
In this SQL: Enterprise Repair Collaboration = Quota Table
LRU = Product Table
Work = same.. :)

SELECT lru.Abbr, [Enterprise Repair Collaboration].Quota AS Contracted, 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, ([Repaired]-[Contracted]) AS [Over/Short], 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 (lru INNER JOIN [Work] ON lru.ID = Work.[LRU ID]) INNER JOIN [Enterprise Repair Collaboration] ON lru.Abbr = [Enterprise Repair Collaboration].LRU
WHERE ((([Enterprise Repair Collaboration].Month) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)) AND ((lru.Enterprise)=True))
GROUP BY lru.Abbr, [Enterprise Repair Collaboration].Quota
ORDER BY [Enterprise Repair Collaboration].Quota DESC;

What are the chances of a sample db to play with?

:( wish I could get that working.... Just haven't had time to get this DB down to a sample sized one to post.. :( I'll try to make time to do that, but right now :( sorry :(

If you just import the relevant tables into a blank db, compact and zip it, does that get it down to size?

Paul
This might work! LoL. Guess I wasn't thinking outside the box :cool:
Instead of just deleting all sorts of data/etc out of the DB I use, I just copied the 3 tables over to a new DB. :D

This should work to get this query issue figured out.. for this example, why wont the query display widget4 information...

Please let me know if you have questions......

dont worry about the number of rows - access will deal with literally millions very quickly, and is comlletely trustworthy. The important thing is to have the right indexes so you can easily select the subset of the rows that you need

eg all records for 2007

access will calculate changes, etc far far quicker than a spreadsheet

I thought I would touch base with this query problem again... I tried re-writing from scratch using two querys. The first one pulled out all the products flagged "Y" under the enterprise column in the LRU, and matched/counted all the listings in the WORK table that matched the criteria. This worked perfectly.
I then tried to join that query with the enterprise quota table. Again, like before, the only records it will return is those that match the date criteria in the Enterprise table.... This was true regardless of the join properties between the two...

I would have thought this would be a LOT easier! LOL Again, i'm SURE i am missing something VERY simple. It's just not clicking.. :)

Sorry, I lost track of this thread. I think generally the problem is the "base" table in your query is the table with the quotas instead of the table with the work. I was trying to write a new query and ran into the fact that the ID field in one table is the ID field, while in the other it's the text field (abbr I think). Is there a reason for that? The lookup fields are a pain by the way:

http://www.mvps.org/access/lookupfields.htm

I added an ID field to that table and this query appears to pull the same data including Widget4:

SELECT Work.[LRU ID], Sum(IIf([work]![status]=4 And [work]![code]=11,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 DateSerial(Year(Work.[TIN Date]), Month(Work.[TIN Date]), 1) = [Enterprise Repair Collaboration].Month AND Work.[LRU ID] = [Enterprise Repair Collaboration].LRUID) LEFT JOIN lru ON lru.ID = Work.[LRU ID]
WHERE (((Work.[TIN Date]) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0))) AND lru.Enterprise = True
GROUP BY Work.[LRU ID];

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!

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?

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

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... :D 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)

:cool: Awsome help Paul... Thanks a million! :cool:

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

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

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?

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:

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.. :) )

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?

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.. :)

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.. :cool:

Happy to help. Post again if you get stuck.

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' :)

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

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

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. :)

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