1 Record = 2 records in Recordset

[accessNator]

How can I achieve this?

In my table I have
Id, Month, Amount, LateFee

i.e.
1,October,10,0
2,November,5,15
3,December,20,0

When I enter my records in a table, I enter the Month, Amount, and if applicable, a LateFee

If I query my records for the month of November, I would like my result to look like this:

2,November,5 <= This indicates an Amount Record
2,November,15 <= This indicates a Late Fee Record

I know, you are asking, why doing you just show as 1 record instead. Well my project is to show separate transactions if there is a late fee activity.

Is this possible?

Thanks in advance,
Chuck

 

[pbaldy]

Sure, with a UNION query. Let me know if you get stuck on it.

 

[boblarson]

It is possible using a Union Query but it would be much simpler if the table were properly normalized to begin with.

To do the Union query you would do something like:

SELECT [Month], Amount, "Amount" As RecordType FROM YourTableNameHere
UNION
SELECT [Month[, LateFee, "Late Fee" As RecordType FROM YourTableNameHere

And you should not be using MONTH as a field name because that is an Access Reserved Word.

 

[boblarson]

Sure, with a UNION query. Let me know if you get stuck on it.

Frizzlefraff! I was still typing when you posted this

 

[accessNator]

Sure, with a UNION query. Let me know if you get stuck on it.

Actually, can you help me with the syntax?

 

[boblarson]

Actually, can you help me with the syntax?

I already did. I was typing it when Paul posted.

 

[MarkK]

- But I think you have a normalization problem because in essence you have FeeType1 and FeeType2 in the same record. How do you calculate total revenue? The sum of two different fields?
- Table should be ...

[B]tTransaction[/B]
ID
Month
Amount
Type

with data like...

1;October;10;Fee
2;November;5;Fee
3;November;15;LateFee
4;December;20;Fee

IMO
Cheers,

 

[boblarson]

- But I think you have a normalization problem because in essence you have FeeType1 and FeeType2 in the same record. How do you calculate total revenue? The sum of two different fields?
- Table should be ...

[B]tTransaction[/B]
ID
Month
Amount
Type

with data like...

1;October;10;Fee
2;November;5;Fee
3;November;15;LateFee
4;December;20;Fee

IMO
Cheers,

And that I definitely agree with (and I stated about the normalization problem in my first response).

 

[accessNator]

It is possible using a Union Query but it would be much simpler if the table were properly normalized to begin with.

To do the Union query you would do something like:

SELECT [Month], Amount, "Amount" As RecordType FROM YourTableNameHere
UNION
SELECT [Month[, LateFee, "Late Fee" As RecordType FROM YourTableNameHere

And you should not be using MONTH as a field name because that is an Access Reserved Word.

Bob and pbaldy
Wow you guys are fast! Thanks. BTW, The actual example was not my original syntax but my question was made to be made simple so I didnt have to type out everything in my actual code. Yes, I wish I could have normalized it more, but this is what it was given to me. Maybe Ill go back and correct it in the near future. Thanks for the tip.

Okay here is something to throw into the mix. WHen I created the "Union", it gave me this.

My original table
1,October,10,0
2,November,5,15
3,December,20,0

Results of the Union Query:
1, October, 10
1, October, 0
2, November,5
2, November, 15
3, December,20
3, December, 0

How do I prevent the query not to display the records if there is no Late fee? I would like it to look like this:

1, October, 10
2, November,5
2, November, 15
3, December,20

Thanks.

 

[pbaldy]

SELECT [Month], LateFee, "Late Fee" As RecordType FROM YourTableNameHere WHERE LateFee > 0

 

[accessNator]

- But I think you have a normalization problem because in essence you have FeeType1 and FeeType2 in the same record. How do you calculate total revenue? The sum of two different fields?
- Table should be ...

[B]tTransaction[/B]
ID
Month
Amount
Type

with data like...

1;October;10;Fee
2;November;5;Fee
3;November;15;LateFee
4;December;20;Fee

IMO
Cheers,

Yes in hindsight, It wasnt normalized all the way.

 

[accessNator]

SELECT [Month], LateFee, "Late Fee" As RecordType FROM YourTableNameHere WHERE LateFee > 0

Ahhh!!!

I see what you mean. I just fix the LateFee > 0 in my Union Query.

That did it!