Append Columns

[aftabn10]

Hi, I was wondering if their is a way to append columns to a table just like you can with rows. i am aware that a join query would work, but i need 1 table to stay static where i can each add an extra column of data that would then show a whole years worth of data.

If somebody could please help, would really appreciate it.

Thanks in advance.

 

[Trevor G]

Perhaps look at creating a query against the 2 tables and then use a make table to give you the table with the extra columns you require.

 

[John Big Booty]

Appending a new column (field) to your table would, on the face of things, appear to go against all the notions of data normalisation you should be able to strip out any years worth of data using a query and the various date functions.

 

[Rabbie]

Following on from Trevor and John's suggestions why not just use a query to show he data in the format you require.

 

[aftabn10]

Thanks treor, john and rabbie. To be honest i want to create a database that will not go against the notions of data normalisation.

The problem I have is that every week I get a new set of data which I am then looking to add at the end of the master table and then show it altogether.

The problem with creating the query against the tables would mean that I would have loads of queries and the make table would then have to be linked to all of these and to be honest I dont know if this will make it a long process.

If there are any other suggestions, then please let me know. Thanks for all your responses guys, really appreciate it.

 

[Trevor G]

Why don't you upload a sample of the database and spreadsheet, indicate the table name and anyother things that may need to be checked, ie the field names you are wanting to see in a table.

I don't see why you would need to create a new query everytime you look to run the import etc.

 

[aftabn10]

Thanks, TrevorG, will upload a sample within the next half hour or so.

 

[aftabn10]

Trevor, as promised, i have attached a sample database.

The sample database shows 3 tables, Qs, Source1 and Source2. I have then ran a union query to join the 2 source tables and then lastly wrote a query (DB) which shows the final table with all the queue information alongwith a weeks worth of data.

The reason i was saying that i would have to have loads of queries is because the final query (DB) links up the Q Table alongwith the Joined Sources and if i kept importing the latest source files then this would only show me 1 weeks worth of data going forward.

Thanks for all your help, really appreciate it.

 

[aftabn10]

TrevorG, just wondering if you had any luck with my file? Sorry dont mean to trouble..

 

[Trevor G]

Im looking at your database, I have a couple of questions though.

In the Db and Qs tables you are showing the dates like WE 21.08.10, which means you are using text fields here, can you not strip away the we and format the field so you can manage the dates?

You mention a make table, but in the sample there is no make table? Your 2 source tables have miss match formats for your time fields.

It would be very useful to follow the database naming convesion otherwise it will be hard to follow your tables and queries. Here is a link.

http://www.databasedev.co.uk/naming_conv.html


Finally from your example it would be useful to establish what you want within the static table, ie field names, data types and formats. Once you can establish this I will see what can be done.

 

[aftabn10]

TrevorG, Thanks for your reply. I will have a look at the database and then come back with a full answer. Thanks once again.

 

[aftabn10]

TrevorG, the following are the answers to your questions:

1. The DB and Qs tables show the "Date Added" column, which is the actual date the queue was formed. The WE can be removed to format this as a date, but this is not linked in any way to the Volumes and appears like this from the source system.

2. I mentioned a make table but said that this might be a longer process as everytime i have a new week I would have to edit the Make Table so thats why I didnt create 1.

3. The 2 source tables both show the time values as seconds and this is how this is picked up from the source system, but again this is something that can be reformatted as "HH:MM:SS" if required, but would prefer for this to stay as seconds.

4. Lastly, the DB query is what i would like the final table to look like as this joins the Q table with the union query "JoinSources" (combination of Source1 and Source2) and gives me the weekly result at the end.

I will post an excel spreadsheet of what i would like within my static table with the correct formats as soon as i can.

Once again thanks for all your help, really appreciate it. Hope that answers your question.