Table Setup Help...

[AC5FF]

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

 

[jdraw]

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

 

[pbaldy]

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

 

[AC5FF]

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]

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?

 

[pbaldy]

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

 

[AC5FF]

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

 

[David Eagar]

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

 

[AC5FF]

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

 

[AC5FF]

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?

 

[pbaldy]

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

 

[AC5FF]

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

=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; [/quote]

 

[pbaldy]

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

 

[AC5FF]

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

 

[pbaldy]

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

 

[AC5FF]

Sample DB Added

Paul
This might work! LoL. Guess I wasn't thinking outside the box
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.

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

 

[gemma-the-husky]

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

 

[AC5FF]

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

 

[pbaldy]

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

 

[pbaldy]

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

=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];