Combining tables based on conditions (1 Viewer)

[ili_sophia]

Hi there,

I have two tables "MaximMainTable" and "ModifyTable" and i would like to combine the two tables to create a new one.

"MaximMainTable" consists of new records created.
"ModifyTable" gets its data from the "MaximMainTable" and the users can modify the records to make it "Additional","Replacement","Revised" under the POtype field.
both tables are updated regularly by the users.

I have to create a table where it consists of all the records from "MaximMainTable" and only records from "ModifyTable" that the POtype field states "Additional" and "Revised"
For the "revised" records, I need it to overwrite the original records in the "MaximMainTable". i have added a DateTimeStamp field in both tables hence the records need to overwrite with the latest stamp.
the records can be uniquely identified with the fields of "GLGPO" and "Line"

I have tried union but it would not work.
is there a way to do this?

 

[isladogs]

Several comments about your request:

1. Why do you want to create a new table and duplicate existing data?
Are you intending to delete the original data afterwards?
If so, why not add the extra records from the Modify table to the Maxim table?

2. Both tables have far too many fields and are not normalised.
You really need to step back and redesign the table structure.

3. Its easy enough to create a query to filter the results you want from the Modify table

SELECT ModifyTable.* FROM ModifyTable
WHERE (((ModifyTable.POType)='Addition' Or (ModifyTable.POType)='Revised'));

You should then use a UNION query to join this to the full table:

SELECT * FROM MaximMainTable
UNION SELECT ModifyTable.* FROM ModifyTable
WHERE (((ModifyTable.POType)='Addition' Or (ModifyTable.POType)='Revised'));

BUT that fails because you do not have the same number of fields in each part of the UNION query.
So you must do one or both of the following:

• remove fields form one or both parts to get identical numbers of fields with the same datatypes in the same order
• add dummy empty fields to one or other parts to get them to match

Only you know which parts of your data should be used so you need to do this for yourself

After that create a make table query to get the new table that you require

BUT going back to the start....WHY do you need to do this at all?

 

[ili_sophia]

I need to create another table because the two tables are used by another creator for their own function hence I cannot combine the tables in the beginning.

but the problem I am facing is that I need the Revised records to overwrite the original records from the "MaximMainTable" instead of just adding it to the table.

is there a way to do it?

 

[Mark_]

As a guess...
1) Add a config record that holds "Last import date".
2) Add an append query that reads through your ModifyTable and adds to NewTable
3) Add an append query that reads through a join on MaximMainTable and NewTable that appends anything not already in NewTable from MaximMainTable.
4) Update config records with now.

Here comes the neat part. Both of your append queries ALSO only add records with a date after the date in your config file.

Sound like what you are looking for?

 

[ili_sophia]

I'm confused about the config record that holds the "Last import date"
could you explain it. Thanks!

 

[plog]

You really need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization)--you haven't set up your tables correctly.

Big picture--you don't move data around a database. You store it in the appropriate table and then reference it via queries. Data shouldn't start in one table then be moved to another if certain conditions are met.

Specific Items:

1. Multiple values in fields. Each piece of data belongs in its own field. My guess is that values like 'MX/RG' and 'MX/SP' in the Mill field represent 2 different values (MX one value and RG/SP another). If that's true, the MX belongs in its own field and the RG/SP value belongs in another. Looks like you do this in a few fields (MXDPO, Fabrication)

2. No primary key. Just so you can uniquely reference a specific record you need a way to identify your rows. You should have an autonumber field in your table for that purpose if no other field fits that purpose.

3. Use the right datatype for fields. GarmentDelDAte smells like a date, but its data type is Short Text. If its a date, make it a date field so you can use it like one. I see other fields that are probably numbers, but are being stored in Short Text fields as well (Width, Amendment, etc.)

4. Numerated field names. When you start suffixing fields with numbers (SketchRemark1, SketchRemark2, etc) its time for a new table. Instead of adding columns of data to accomodate data, you should be adding rows. If you have 5 SketchRemarks you make a new table and add 5 rows of data to it, instead of putting data in 5 columns.

Again: https://en.wikipedia.org/wiki/Database_normalization

 

[Mark_]

A configuration record is a single record in a table that holds multiple values that need to be maintained (either for a given install or system wide) between sessions. Normally this holds things like "Company Name", or the default path to save reports to. In most cases you would load the record when you start the application and simply reference the values.

For timed events or "last run" type info you update the record.

 

[Mark_]

@OP,

I think I've seen a similar format for your data recently... Is this part of an import from another system?

 

[ili_sophia]

Thanks plog for informing me about this. I will make the necessary changes however, some of the fields are presented this way because the data is actually imported from another system so i cannot make changes it.

 

[ili_sophia]

@Mark

does this mean that it will only append records with the lastest run time?

 

[Mark_]

@Sophia,

What is your goal with this? Do you simply import once and leave the data in the format it comes in? Or do you have an import method with an end goal of putting data into a more normalized format for future use?

 

[ili_sophia]

i have to import the data every day from an excel worksheet as it comes from another system. With the imported data, the users of this access system can amend the data for certain columns such as MRName, Nettweight, Grossweight, etc... once it is completed it will be saved under the MaximMainTable and if they do any modifications afterwards, it will be saved under the modifytable. I need to combine the two tables together with the conditions to be able to export it out to excel for other reports.

 

[Mark_]

I would recommend looking into normalizing your data, mostly to make your reporting needs easier.

Import to a "From the excel" table then allow users to modify. Once done, read out of it and start putting into a normalized format. From there you don't have a "Modify table", more of a "Log of updates" and an easy time finding, sorting, and reporting.

When you do need to export you can customize the data easily for each need. Putting a properly normalized database into, effectively, flat files isn't hard. It will also mean your database runs much smoother.