Field Names in Table

[Guest]

I create a table with a vendor and 10 fields that contain their current
balance due in each week (Week 1, Week 2..Week 10). I then export this table
to an Excel spreadsheet. I also have a table that has a date for each week. I
would like the column headings to be the actual date, not "Week 1". The dates
can change everytime this macro is run. Thank you.

 

[Guest]

Change your table structure to this --
BalSheetID - autonumber - primary key
vendor - text
BalanceDate - DateTime
Balance - Number - decimal
Remarks - text

Then use a crosstab query with the date range of the number of weeks you want.

 

[Rick B]

This is NOT a normalized database design. A field name should not contain
data (week 1, week 2, etc.) You are trying to build a spreadsheet in
Access. Access is not a spreadsheet application.

The proper design would involve two or more related tables. For example,
you might have the following:

TblVendors
VendorNumber
VendorName
VendorAddress1
VendorCity
VendorState
etc.

TblTransactions
TransDate
TransVendorNumber
TransType
TransAmount

TblTransactions
TransType
TransDescription


You could then create quireies and reports to pull all the transactions in
and group them and sort them by vendor and week. Using a schema like the
one above allows many different reporting options and allows for you to
grow. In your current schema, you would have to rebuild your table (add a
new field) every single week. Also, you say that your fields are named
"week1, week2, etc., what happens next year? You aren't going to create a
new table are you? That is even LESS Normalized.

I'd suggest you do some searches and read up on normalized database
structure and design before you go any further.

 

[John Vinson]

I create a table with a vendor and 10 fields that contain their current
balance due in each week (Week 1, Week 2..Week 10).

That's a good spreadsheet design... and a TERRIBLE table design.

I then export this table
to an Excel spreadsheet. I also have a table that has a date for each week. I
would like the column headings to be the actual date, not "Week 1". The dates
can change everytime this macro is run. Thank you.

I'd suggest restructuring your tables, and using a Crosstab query to
generate this wide-flat structure. A tall-thin table with fields for
VendorID, DueDate, and AmountDue could easily be displayed using a
crosstab query, with VendorID as the RowHeader and DueDate as the
ColumnHeader; this query could then be exported to Excel, and would
contain the actual dates.

John W. Vinson[MVP]

 

[Guest]

Thanks all. Sometimes you have to look at something from another angle.