How do I append to a "variable" field?

[NKA]

I don't know how to explain this in simple terms - but I will try...

I run a report monthly based on data received in Excel.

There are several queries, all of which do the same thing every month, but I need to change a field to recognise the newest month.

Is there any way, either in queries, or with code (which is not my strong point) where I can tell the database to append to a "variable" - for example. Rather than telling the query to append [days] to [Nov04] (and then change Nov to Dec the next month) - I want to be able to enter the month in a parameter - so when you run the query, it asks you for the month (which is the field to append to).

NKA

The answer's always easy - if you know it! ;-)

 

[dhookom]

What does your Excel data look like and what do you want your Access data to look like? Please take some time to enter a few records in a reply.

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]

 

[NKA]

Hi there,

Excel data is linked each month into Access. Fields are:

[Dept_ID] - Text
[Department] - Text
[Emp_No] - Text
[Emp_Name] - Text
[Days] - Number

Currently these are appended to a master table. Fields are:

[Dept_ID] - Text
[Department] - Text
[Emp_No] - Text
[Emp_Name] - Text
[Dec04] - Number (This is taken from [Days] above)
[Nov04]
[Oct04] etc etc etc etc

The report is based on a query of the master table (to include totals for the 13 month rolling period).

The Report looks something like this:

Dept Name Dec04 Nov04 Oct04 Sept04
123 Joe Bloggs 0 1.5 0 2
123 Fred Smith 1 0 0 0
----- ----- ----- ----- 1 1.5 0 2

456 Mary White 0 0 1 0
----- ----- ----- -----
0 0 1 0

I hope this helps!
Many thanks

NKA

The answer's always easy - if you know it! ;-)

 

[lespaul]

the reason you are having such difficulties is due to the fact that your table isn't normalized (see Fundamental of Relational Database Design for more information on normalization)

Leslie

 

[dhookom]

You can display formatted data by enclosing your data in [ignore][tt] and [/tt].[/ignore]

Please don't expect others to accept an email with your database. Learn how to identify and describe your issue. Then take the time to read and undertand responses. Leslie hit the nail on the head with his "normalized" comment. If you don't understand what that means then research and ask.

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]