MS Access Combining fields into a longer one

[Sidran]

Hello guys. This is my first post so excuse any mistakes that i have or will make.



So i wish to combine multiple fields (there are no fixed number of fields, they vary depending on the data, so i guess union queries are out of the question) into one large field.
For example:

TABLE 1:

PNumber	PName	C1	C2	C3
1	AAA	0.1	0.2	0.3
2	BBB	0.4	0.5	0.6

So i wish to combine the fields C1, C2 and C3 into a larger fields containing all the data. So considering the example above, it should look like this:

TABLE 2:

PNumber	PName	C1+C2+C3
1	AAA	0.1
1	AAA	0.2
1	AAA	0.3
2	BBB	0.4
2	BBB	0.5
2	BBB	0.6

I plan on entering data into TABLE 1 using a form and running a query, or some code etc so that it looks like TABLE 2.

Thanks in advance

[lfpm062010]

The UNION query will be your answer.

[June7]

Why are you entering data into a non-normalized structure to begin with? How can the number of fields vary? The table is set up with 5 fields - why would that change? Regardless of whether or not you enter a value into a field, the table still has 5 fields.

As Ifpm points out, UNION is the means to rearrange the data to normalized structure. The empty fields will be displayed in the query.

[Sidran]

Ifpm062010, how could i use a union query if the number of fields vary each time?

[Sidran]

June7. The data is entered into the table through a form. I want the layout of the form to look like Table 1 and so i made the form from Table 1 and decided on changing the format of Table 1 into Table 2.

The field names are dates. These dates were entered, as records, into a table (I will call this the primary table) and then by using a cross tab query, I managed to turn the records into the field names which are input into Table 1. As you can imagine, the amount of records in the "Primary table" can vary, and as a result the number fields in Table 1 will vary as well.

You did mention using a union query, but i doubt a union query can be used if the fields vary. I wish to automate the process through the push of a button and so I cannot update/change the SQL in the union query every time a new record is entered.

I hope these answer are what you require. Please do not hesitate in clearing any further doubts that you may have. Thanks for the quick replies.

[June7]

I am confused by "by using a cross tab query, I managed to turn the records into the field names which are input into Table 1". If you used a crosstab to achieve Table 1 then the raw data must already be in Table 2 structure?

Options to convert Table 1 to Table 2:

1. UNION query - which you say won't work

2. normalized data structure (Table 2) for raw data entry to begin with

3. VBA code

[Sidran]

So there is a table with 1 field called "Date" under which the Monday date of every week starting from 7th July 2014 is input (from excel). I then use a query to take out the dates that I require. This is done by using the "Between _____ and _____" function which is input into the query by the means of a form. Then using a crosstab query I turn these values into field names (so the values eg. 7-Jul-2014, 14-Jul-2014 etc are row heading of the crosstab query, making them the field names), this is how I got to Table 1. Since i want the layout of my form to look like the layout of Table 1, eg. the dates going across the top, I made the form, using a wizard, from Table 1. So this is how the form would look (exactly like Table 1):

Form 1

PNumber	PName	C1	C2	C3
Data	Data	Data	Data	Data
Data	Data	Data	Data	Data

I want the form to look like Table 1 cause it looks more appealing to the eye. Table 1 is a temporary table (since the fields will be added on and removed as the dates "Between _____ and _____" change) and so the data entered into the form cannot be permanently stored in Table 1. So I need to transfer the data from Table 1 to Table 2 in order to store it permanently.

As for the VBA, I'm very new to access and know little to nothing about VBA, I did try to do something with loops but it didn't work out. If you could possibly point me in the right direction with VBA I would be willing to research into it and find out more about it.

Thanks and sorry for the late replies, i keep forgetting that I have a post on the forums.

[lfpm062010]

Ifpm062010, how could i use a union query if the number of fields vary each time?

I am confused. Your original request only have one 3 output fields. Did you change your question?

[June7]

ifpm, OP stated in initial post that the number of fields varies, which is the result of a CROSSTAB, as indicated in post 5.

But that does confuse me because if Table1 is the result of a crosstab then the data must have started from table2 structure.

If you want to change your data structure for data entry like table1, can't help you. That is a non-normalized structure and will cause you all kinds of problems. The first of which you already identified - you don't know how many fields to accommodate.

[lfpm062010]

So, the Table1 is the result of the crosstab. If that is the case, what is the table/query design that create the crosstab? Maybe there is a way to use that to create Table2.

[Sidran]

Before I close this thread. Just want to make everything clear and see if there is any possibility of an answer before i back down.

So lets imagine this is the table i started off with

Table A (The dates are imported from excel, the dates are the Monday of every week starting from 7/7/2014):

Date

7-Jul-2014

14-Jul-2014

21-Jul-2014

Using a crosstab query, I made Table A into Table B

Table B:

7-Jul-2014 (C1)	14-Jul-2014 (C2)	21-Jul-2014 (C3)

To Table B I add 2 more fields (Will always be 2 fields) by using a query with the follow SQL:
ALTER TABLE [Table Name] ADD COLUMN PNumber Numeric(25)
and
ALTER TABLE [Table Name] ADD COLUMN PName Text(25)

So the result is Table C.

Table C:

7-Jul-2014	14-Jul-2014	21-Jul-2014	PNumber	PName
0.1	0.2	0.3	1	AAA
0.4	0.5	0.6	2	BBB

To Table C, data from the form is entered. Now suppose after this, I add another row to Table A with the values "28-Jul-2014", after running the queries, Table C will have an additional column. Due to this, Table C is a temporary table (with varying numbers of fields) and so i cannot store data in this table, as a result I need to move the data in this table elsewhere. That's where Table D comes in.

Table D:

PNumber	PName	Date	Value
1	AAA	7-Jul-2014	0.1
1	AAA	14-Jul-2014	0.2
1	AAA	21-Jul-2014	0.3
2	BBB	7-Jul-2014	0.4
2	BBB	14-Jul-2014	0.5
2	BBB	21-Jul-2014	0.6

And that's how it works. That's why I need Table D. If you need more clarification in any part, please do let me know. If this can be done in any other way, do tell. Thanks

[June7]

Why import Monday dates from Excel into table? A VBA procedure can populate records in table.

I recommend you do data entry to a normalized table structure. Use form/subform arrangement. Main form bound to the table of Monday dates and subform bound to Table D.

Otherwise, lots of VBA code.

[Sidran]

June7, what you said makes perfect sense, and I have no idea why I didn't think of it... But i think there is a tiny problem. In Table D I want a fields called Dates, which as you mentioned above will be in the main form. How could i link the main form to the subform in such a way that all data entered below "7-Jul-2014", will be saved in Table D with the date being 7-Jul-2014 and all the data entered below "14-Jul-2014" will be saves in Table D with the date being 14-Jul-2014.

The answer you said about seems like the prefect solution apart from the problem I mentioned in the previous paragraph, if there is any way to solve this problem, please do tell me. Thanks

[June7]

Options:

1. dates are not saved into table D - table A has an autonumber primary key, this PK is saved as FK in table D

2. dates are primary key in table A and will be saved as FK in table D

In either case, the form/subform arrangement will automatically save the FK.

[Sidran]

Really not familiar with Primary and foreign keys. How will setting the dates in Table A as a primary key help populate the date field in Table D?