Not sure how to query this

[Jennifer]

Hello. I'm using Access 97. I have two tables, one with
account information and another with billing information.
In the account table, each account (customer) is listed
once with their own unique account number. In the billing
table, an account can be in their multiple times just
depending on how many months they have a billing
outstanding for.

I would like to populate a table with the account, and
then each month they have an outstanding bill. Example:

Account Month1 Month2 Month3
123456 01/31/03 02/28/03 03/31/03
789152 02/28/03 03/31/03 04/30/03

I just can't figure it out. Please help.

Thanks.

 

[Matt]

Hello. I'm using Access 97. I have two tables, one with
account information and another with billing information.
In the account table, each account (customer) is listed
once with their own unique account number. In the billing
table, an account can be in their multiple times just
depending on how many months they have a billing
outstanding for.

I would like to populate a table with the account, and
then each month they have an outstanding bill. Example:

Account Month1 Month2 Month3
123456 01/31/03 02/28/03 03/31/03
789152 02/28/03 03/31/03 04/30/03

I just can't figure it out. Please help.

Thanks.

Hi Jennifer,

You can use a make-table query to populate a table, if that's what you
want to do. You can also use a select query as a source for reports
and forms, though, and this requires fewer steps.

A couple of questions:
1) Are your two tables linked one-to-many on the Account field? (I
assume that they are.)
2. It would help to know what you plan to do with this table.
Arranging Month1, Month2, Month3, etc is generally a bad idea, because
columns are expensive and because you need to be able to anticipate how
many months you will have. It would be easy, however, to arrange a
form to output a series of records in this format.

Get back to me, and I'll try to help you as best I can.

Matt

 

[John Vinson]

Hello. I'm using Access 97. I have two tables, one with
account information and another with billing information.
In the account table, each account (customer) is listed
once with their own unique account number. In the billing
table, an account can be in their multiple times just
depending on how many months they have a billing
outstanding for.

I would like to populate a table with the account, and
then each month they have an outstanding bill. Example:

Account Month1 Month2 Month3
123456 01/31/03 02/28/03 03/31/03
789152 02/28/03 03/31/03 04/30/03

You shouldn't have such a non-normalized Table in your database; but
you can generate a Query to display the data in this way using a
Crosstab query.

 

[Jennifer]

-----Original Message-----


You shouldn't have such a non-normalized Table in your database; but
you can generate a Query to display the data in this way using a
Crosstab query.


.

I thought that the tables should be broken down that way.
Should it be better to have the billing months in the
table with the customer information?

I don't think I can use a crosstab query for this, I've
already tried that.

 

[John Vinson]

an account can be in their multiple times just
depending on how many months they have a billing
outstanding for.

The proper design for this table would be tall-thin: fields named
Month1, Month2 and so on are "spreadsheet" not relational.

AccountNo BillDate AmountDue


This can be used with a Crosstab.

 

[Matt]

My tables are linked one to many on the account field.

The months could be anywhere from three to five months. I
am needing to populate a table to run a mail merge on with
Word. The user selects the accounts they need to send a
letter to, based on that the queries need to populate the
table with the selected accounts, and their various
billing months. I actually work for a tax department and
these "accounts" are for monthly sales taxes that aren't
paid.

Any help is appreciated.

 

[Matt]

My tables are linked one to many on the account field.

The months could be anywhere from three to five months. I
am needing to populate a table to run a mail merge on with
Word. The user selects the accounts they need to send a
letter to, based on that the queries need to populate the
table with the selected accounts, and their various
billing months. I actually work for a tax department and
these "accounts" are for monthly sales taxes that aren't
paid.

Any help is appreciated.

Hi Jennifer,

Hmm...I'm afraid that I have little experience in this area. I've
never tried to move records into separate columns before, so I am not
sure how much help I can be to you. I will think on it and do a little
bit of research, but in the meantime you will get better help from John
Vinson, who has helped me many times before.


Matt

 

[Jennifer]

-----Original Message-----



Hi Jennifer,

Hmm...I'm afraid that I have little experience in this area. I've
never tried to move records into separate columns before, so I am not
sure how much help I can be to you. I will think on it and do a little
bit of research, but in the meantime you will get better help from John
Vinson, who has helped me many times before.


Matt
.

Thanks very much for your help. I ended up getting it to
work through using code and a recordset.

Thanks again,
Jennifer