need help with reorginizing a table with a query (1 Viewer)

[romashka1]

Hello everybody,

I'm relatively new to the Access and I never worked with VB or macros. I have the following problem:

Currently I have a table with the following info:
AgentNumber I StartDate I N200106 I N200105 I N200104 I ..... I N199501,

where in fields N199501 - N200106 I have payments for the appropriate month to the agents.
Having 6/2001 as current month I need to get a table with the information:

AgentNumber I 0MonthPayment I 1stMonthPayment I 2ndMonthPayment I .... I nmthMonthPayment, not to exceed 36 months

For example:
AgentNumber I StartDate I N200106 I N200105
1 5/15/01 100 150

should become

AgentNumber I 0MonthPayment I 1stMonthPayment
1 150 100

I figured out how to create a union query, but I would need to union 36 little queries..

Is there an easier way to do it?

Here is the actual query I made:

SELECT NEFYCDATA.AGT_NO, NEFYCDATA.PRODUCT_CD, [nm]-0 AS NYLIC_yr, NEFYCDATA.N200106 AS payment
FROM NEFYCDATA INNER JOIN [date test] ON NEFYCDATA.AGT_NO = [date test].AGT_NO
WHERE ((([date test].nm) Between 0 And 36))
GROUP BY NEFYCDATA.AGT_NO, NEFYCDATA.PRODUCT_CD, [nm]-0, NEFYCDATA.N200106;
union
SELECT NEFYCDATA.AGT_NO, NEFYCDATA.PRODUCT_CD, [nm]-1 AS NYLIC_yr, NEFYCDATA.N200105 AS payment
FROM NEFYCDATA INNER JOIN [date test] ON NEFYCDATA.AGT_NO = [date test].AGT_NO
WHERE ((([date test].nm) Between 1 And 36))
GROUP BY NEFYCDATA.AGT_NO, NEFYCDATA.PRODUCT_CD, [nm]-1, NEFYCDATA.N200105;
union and so on for 36 times...

Thank you!

 

[Pat Hartman]

To solve your problem, you really need to fix your table design. You have a 1-to-many relationship. One agent to many payments. The payments need to be stored in their own table.

tblPayment
AgentNumber (primary key part1)
PaymentDate (primary key part2)
PaymentAmount

Use a subform to maintain these payment records. If you want to look at an application that uses this concept so you can get a feel for what the queries look like and how the forms work, look at Northwinds. The order to order detail is a one-to-many relationship.

 

[romashka1]

Pat,
thank you for your reply.
Yes, I know the table is far from perfect. Actually I don't use forms and I don't really maintain the database, I get a current state of it as of certain date and then use it to analyze the data from it. Somebody modified even that initial database so unfortunately at the moment I have only the far-from-perfect table that I described before. So -
is there a way to solve my problem without any restructuring of the table, just based on what I have at the moment?
Thanks!

 

[Pat Hartman]

The table structure is not just "far from perfect", it is hopeless and your proposed layout won't work any better.

From the way the fields are named, it looks like the columns change every month. Just try to write SQL to deal with that! Who adds these new columns? Where does the data to populate them come from? If someone can take the time to modify this table structure and write new update queries every month, surely someone can take the time to re-structure the table properly and be done with the monthly modifications. If you don't have time to do something right, what makes you think you have time to do it twice (or in your case monthly)?

The nice thing about not getting paid to answer questions in this forum is that I don't have to provide solutions to problems that shouldn't exist. Good luck.

 

[raskew]

As Pat Hartman -what a guy-- so subtly hinted, this is a disaster in progress. Suspect that it has evolved from a convoluted spreadsheet solution. It is never, in its present form, going to get any better-only worse and more convoluted!

In your explanation, you never mention field [product_cd], but the code seems to indicate that it may play a prominent role.

Question: When analyzing the data, are you looking at:

(1) Payments to agents from all sources (product_cd immaterial), or
(2) Payments to agents, broken down by product_cd

If you'd respond to this, think I can provide code to restructure/normalize your table, either temporarily or permanently, as you desire.

Bob