Grouping data from different fields together 2

[mlstewart]

Here are my fields:

Month
Desc1
Desc2
Desc3
Amt1
Amt2
Amt3


Desc1 goes with Amt1
Desc2 goes with Amt2
Desc3 goes with Amt3


All of these are saved in one record.

In the table it will look like this with several records:

Month Desc1 Desc2 Desc3 Amt1 Amt2 Amt3
Jan Apple Ball Coat $2.00 $5.00 $4.00
Feb Ball Coat Gown $5.00 $4.00 $3.00
Mar Dog Egg Fish $1.00 $3.00 $6.00
Jan Ball Coat Gown $3.00 $4.00 $2.00


In a report, I am grouping by month but I also want to group by the data entered in the Desc1, Desc2, and Dec3 fields but I don’t know how to do that. From the data in the table above, the two records with the month of Jan…..


Month Desc1 Desc2 Desc3 Amt1 Amt2 Amt3
Jan Apple Ball Coat $2.00 $5.00 $4.00
Jan Ball Coat Gown $3.00 $4.00 $2.00



Keep in mind that it goes together like this:

Apple $2.00
Ball $5.00
Coat $4.00


Ball $3.00
Coat $4.00
Gown $2.00


…..will be grouped together along with the data in the Desc fields. On a report, I want it to look like this:



Jan
Apple $2.00
Ball $8.00
Coat $8.00
Gown $2.00

Feb
Ball $10.00
Coat $2.00
Gown $3.00

March
Dog $1.00
Egg $3.00
Fish $6.00


The report groups by month which I know how to do that part. What I don’t know how to do is group the data entered in the Desc1, Desc2, and Desc3 fields. Is it possible to group the data in different fields like I am asking about here.

 

[GingerR]

I suggest you restructure your table so that it adheres to the rules of relational database design. You will continue to have problems like this forever. Also, what if you then need more Descriptions and Amounts? Also, how do you distinguish between JAN of 2005 and JAN of 2006? When you sort on MONTH, it will sort alphabetically instead of by date so that DEC will come before JAN.

It could be more like this:

tblMain
Field: Month (Date) m/d/yy where d = 1 (or make month and year fields separately)
Field: Desc (text)
Field: Amount (double)

Data:

1/1/05 Ball 3.00
1/1/05 Egg 3.00
1/1/05 Ball 5.00
1/1/05 Apple 2.00
2/1/05 Ball 7.00

So you can see that it's now easy to GROUP by any of the fields, and the date/month will go in the correct order. On reports and such you can just format it to show "01/05" for Jan 2005 if you want.





Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dhookom]

I don't know if the OP thinks you deserve a star but I think you do!

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mlstewart]

Thanks for the reply. The database is actually more extensive than the example I am using. In the table, there aren't any dates. Only a Month field which will contain January-December. Also, it will only be for one year so you don't have to worry about whether it is 2005 or 2006. I already have it set up to place the records in order from January to December so that is taken care of as well. I don't mind re-structuring my table but I have to set it up so that the individual can enter several descriptions and the amounts that go with those descriptions. The other thing is that those descriptions and amounts the user enters must stay with the other fields in the table that aren't listed here in the post. I need Desc1, Amt1, Desc2, Amt2, Desc3 and Amt3 to stay together because that same individual will later be entering more Descriptions and amounts that will need to be kept together but separate from the 1st group of descriptions and amounts they entered. This probably isn't the best description of what I am trying to accomplish but I really appreciate your help.

I was going to set it up as two different tables using your suggestion to set up the table for the descriptions and amounts. The first table will contain fields that aren't listed in this post and the second table will contain the descriptions and amounts. Where I get confused on the suggestion you made is when I set up the form for the user to enter the descriptions and amounts, how can I allow them to enter more than one. If I only have one Desc field and one Amt field then I can only put one text box for the Desc and one for the Amt on the form. How can I have several Desc and Amt text boxes for them to enter their info with only one Desc field and one Amt field?

 

[GingerR]

Rule of thumb: if you start repeating a field name (DESC1, DESC2, etc), it's time for a new table.

What are "the other fields"? Are they related to a MONTH?

Look up SUBFORMS. You'd have a form based on the MONTH table I guess (although that still doesn't make much sense to me) and a subform of the Desc and amt. The second table will have MONTH, DESC and AMOUNT, making master/child links to the MONTH field in the MONTH table. On the form, he DESC can even be made into a combo box, which feeds off of a list of Items you keep in another table.

Another suggestion is to include year in the same table. Do you end up having a table for 2003, 2004, 2005 etc? This can make future calculations troublesome.

Tell me more about "the other fields" as this may affect what I've told you.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mlstewart]

The sample data I gave in the original post actually has nothing to do with the data in my table. I used that sample info so that the data would be easier to work with. Anyway, my table stores data related to an idividual's jobs for one year. The individual travels from one job site to another and has certain info he has to keep up with (hours worked, lodging, mileage, job description, etc). All of this is entered into a form by month. For each job the individual has certain funds that are paid by the employer (paid monthly). Sometimes the individual will work at two different job sites in the same month. This is where the original post comes in. These funds vary from job to job. Sometimes they may be the same and sometimes different. Each fund has a description and an amount paid in. There is no way for me to know what those funds will be so they have to be able to enter the descriptions and the amounts. They need for the funds paid to them to be kept with the other data in the table (hours worked, lodging, mileage, job description, etc). They also want a report that sums up those funds. For example, for January, there are two entries with some of the same funds and they want the funds that are the same to be summed up for that month. The problem is that the funds that are the same may not be in the same field. (Lets say that for one record in the Desc1 field they've entered "Training Fund" and in another record for Desc3 field they've entered "Training Fund"). I don't know how to set it up so that I can group the data that is the same but in different fields. The individual needs to be able to enter up to 12 descriptions and 12 amounts which will be kept together so that they know which job those funds are for. I may just start over on the design of the database and try doing it a differnt way. I was trying to set it up just like he wanted it. So far, this is the only problem I've run into with this database. Thanks for your help.

 

[GingerR]

Sounds like you're leaving us? We are just beginning!!

Setting it up the way "he wanted it" is sort of like setting up the way Excel looks, right? But that won't work - it's not how a relational db is built, and it will be a pain for you forever. Put information that is related into the same table:

So how about this:
Table: Employees
Field: EmployeeID
Field: EmployeeName
{other data related to an employee}


Table: JOBS
Field: JobID
Field: JobName
Field: JobLocation
{other data related to a Job}


Table: Charges
Field: EmployeeID (from table above)
Field: JobID (from table above)
Field: Month
Field: Item
Field: Amount


Sample Data:
EMPLOYEES
1 John Smith
2 Steve Jones
3 Marsha Brady

JOBS
1 Costco Wholesale Issaquah, WA
2 Walgreen's Seattle, WA
3 Safeway Foods Jonestown, OR

CHARGES

EmpID   JobID   Month    Item    Amount
1         1      JAN    Phone   $34.77
1         2      JAN    Gas     $18.76
2         2      JAN    DSL     $45.45
1         2      FEB    Gas     $32.19

Do you see the relationships? There is one of each employee. There is one of each "jobs". One employee may go to many jobs, and each job may have many employees go to it. An employee may charge for many items, and each item may be charged to by many employees.

Also do you see how you can add an endless number of Employee+Job+Month combinations? Later your totalling/grouping queries will be a snap.

Now if you want to control what the "Items" are, or make them pickable from a list as well, you'd make a table for them too.

Table: Items
Field: ItemID (autonumber)
Field: Item (text)

Sample Data:
1 Gas
2 Phone
3 Lodging

then they could pick from a list so you can group them better (nothing worse than people spelling the same thing four different ways). If they are doing something that's not on the list, you can add some simple code that will add the new entry to the list.

Is this the kind of thing you are looking for? Where you'd go from here is this: Make a form based on Employees table. Make another form based on the Charges table. Embed (make it a subform) the 2nd (Charges) form into the first (Employees) form. Make the master/child links be EmployeeID. Now the charges for an employee are right there, and anything you add to the charges table will be attached to that employee. You can make a combo box for Job Number, fed from the JOBS table, so the job is pickable. Same thing for ITEM if you want. Easy squeezy.

How's that sound?



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mlstewart]

Thanks for the suggestion. The database I am working on will only keep track of one individual which eliminates the need for for the Employee ID field. Other than that, you've given me a great start on re-doing my tables. I really appreciate your help...you definitely deserve a star! Thanks!

 

[GingerR]

No problem. I'm glad you are "getting it". It's the basics of good design. Post back if you need any other help!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html