Hello folks,
It’s been a really long time since I posted in the forums. From the help I got here I was able to create some really creative SQL queries. Usually after a while I can manage to create them but this one is really hurting my noggin, and I guess It’s a good time to learn a new SQL skill.
My problem.
I’m migrating an Excel created “Database” The horror!! To a proper database.
I’ve hit a minor snag.
I’ve separated most of the data logically but I just found out that one of the columns references a number from that row’s previous rendition.
Hard to explain so let me show you the schema.
I have a table that has a combined Primary Key
The first key is a number ‘like’ 1, 2, 3. This number references another table which gives dates related to this data. And what those dates also mean.
And the second key is a different id that references the data value on another table.
(So Basically a Many to many table)
This table has additional fields that are pertinent to this.
One of the fields is Value, which currently is empty because they were using this column in excel to hand write the values
The second field is Last Value, so that the person filling out the sheet can see how many were there the last time the query was run. (Since this was excel I use the word query loosely)
So digitally the only column populated was last value but that references the previous date.
So I would like to move the value from this column, to it’s same second ID but to the previous first id that corresponds to it’s previous date.
Now I’ve made another table which the first column contains the id of the first column in the previous table, and the second table represents the id where I want the data to be moved.
I’m not sure if I’m explaining myself correctly… Here is a visual:
I want to take this
[table=“width: 500”]
[tr]
[td]DateID[/td]
[td]ItemID[/td]
[td]Value[/td]
[td]Last Value[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[td]null[/td]
[td]34[/td]
[/tr]
[tr]
[td]3[/td]
[td]1[/td]
[td]null[/td]
[td]22[/td]
[/tr]
[tr]
[td]4[/td]
[td]1[/td]
[td]null[/td]
[td]12[/td]
[/tr]
[/table]
And turn it into
[table=“width: 500”]
[tr]
[td]DateID[/td]
[td]ItemID[/td]
[td]Value[/td]
[td]Last Value[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]34[/td]
[td]null[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[td]22[/td]
[td]34[/td]
[/tr]
[tr]
[td]3[/td]
[td]1[/td]
[td]12[/td]
[td]22[/td]
[/tr]
[tr]
[td]4[/td]
[td]1[/td]
[td]null[/td]
[td]12[/td]
[/tr]
[/table]
And I also made this table to help with the query
[table=“width: 500”]
[tr]
[td]CurrentDateID[/td]
[td]PreviousDateID[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[/tr]
[tr]
[td]3[/td]
[td]2[/td]
[/tr]
[tr]
[td]4[/td]
[td]3[/td]
[/tr]
[/table]
I seem to be having a problem appending the data where I want to. My attempts have simply added new rows where the third column was populated and the rest were null.
I’ve tried to just get a query going but even that seems to be difficult as I want to match the first two ids otherwise my data won’t be precise.
SELECT
CurrentDatetoPreviousDate.PreDateId,
[Table].DateId,
[Table].ItemID,
[Table].Priority,
[Table].LastValue,
[Table].Value,
[Table].IN,
[Table].OUT
FROM
[Table]
INNER JOIN
CurrentDatetoPreviousDate
ON
[Table].DateId = CurrentDatetoPreviousDate.DateId
WHERE
(
(
([Table].DateId)=(
SELECT CurrentDatetoPreviousDate.[Last Edition]
FROM [Table] INNER JOIN CurrentDatetoPreviousDate ON [Table].DateId = CurrentDatetoPreviousDate.PreDateId
WHERE
((([Table].ItemID)=[PlaceID]))))
AND
(([Table].ItemID)=[PlaceID]
)
);
I know this is possible using SQL but I think I’m going the wrong direction. Any hints??