Please help!

  • Thread starter Thread starter Santi
  • Start date Start date
S

Santi

I want a master table to have the most up to date information which is based
on an excel file that I get on a monthly basis that has existing items which
has to be updated and, new ones which are not part of the master table but
that I want to include. What structure, queries or tables do I need to make
this happen? Any guidance is greatly appreciated.

*I want to be able to update and include new information if needed on the
following common fields: Item number, Description, Unit price and discount
 
I would suggest importing the excel file each month to a staging table, then
running queries to update existing records and add new records to the master
table.

The first time, just import the Excel file into a new table. After that,
you can run a query to delete all records from the staging table, and then
run an import into the existing table.
 
Santi,

Before you try this, backup your database.

When faced with this situation, I generally import the data from the Excel
file into a temporary table.

I then write an update query to update the data in your master table from
the temp table, for those items that are in both, something like:

UPDATE tbl_Master
INNER JOIN tbl_Excel
ON tbl_Master.[Item Number] = tbl_Excel.[Item Number]
SET tbl_Master.[Description] = tbl_Excel.[Description],
tbl_Master.[Unit Price] = tbl_Excel.[Unit Price],
tbl_Master.[Discount] = tbl_Excel.[Discount]

You can then write an append query that will append any new records from
tbl_Excel into tbl_Master.

INSERT INTO tbl_master ([Item Number], [Description], [Unit Price],
[Discount])
SELECT [Item Number], [Description], [Unit Price], [Discount]
FROM tbl_Excel
LEFT JOIN tbl_Master
ON tbl_Excel.[Item Number] = tbl_Master.[Item Number]
WHERE tbl_Master.[Item Number] IS NULL

An alternative method is to create what some people call an UPSERT query,
which is basically an update query which uses a right join rather than an
inner join. Because of the right join the result is that Access will update
those records that already exist, and insert those that don't, all in one
step.

It would look like:

UPDATE tbl_Master
RIGHT JOIN tbl_Excel
ON tbl_Master.[Item Number] = tbl_Excel.[Item Number]
SET tbl_Master.[Description] = tbl_Excel.[Description],
tbl_Master.[Unit Price] = tbl_Excel.[Unit Price],
tbl_Master.[Discount] = tbl_Excel.[Discount]
 
Dale,

Thank you for the valuable information, but I tried doing the Upsert query
that you suggested using your example and I get the following message: the MS
Access database engine cannot find the input table or query 'tbl_Master'.
Make sure it exists and that its name is spelled correctly.

DO YOU KNOW WHY I GET THAT MESSAGE?

Dale Fye said:
Santi,

Before you try this, backup your database.

When faced with this situation, I generally import the data from the Excel
file into a temporary table.

I then write an update query to update the data in your master table from
the temp table, for those items that are in both, something like:

UPDATE tbl_Master
INNER JOIN tbl_Excel
ON tbl_Master.[Item Number] = tbl_Excel.[Item Number]
SET tbl_Master.[Description] = tbl_Excel.[Description],
tbl_Master.[Unit Price] = tbl_Excel.[Unit Price],
tbl_Master.[Discount] = tbl_Excel.[Discount]

You can then write an append query that will append any new records from
tbl_Excel into tbl_Master.

INSERT INTO tbl_master ([Item Number], [Description], [Unit Price],
[Discount])
SELECT [Item Number], [Description], [Unit Price], [Discount]
FROM tbl_Excel
LEFT JOIN tbl_Master
ON tbl_Excel.[Item Number] = tbl_Master.[Item Number]
WHERE tbl_Master.[Item Number] IS NULL

An alternative method is to create what some people call an UPSERT query,
which is basically an update query which uses a right join rather than an
inner join. Because of the right join the result is that Access will update
those records that already exist, and insert those that don't, all in one
step.

It would look like:

UPDATE tbl_Master
RIGHT JOIN tbl_Excel
ON tbl_Master.[Item Number] = tbl_Excel.[Item Number]
SET tbl_Master.[Description] = tbl_Excel.[Description],
tbl_Master.[Unit Price] = tbl_Excel.[Unit Price],
tbl_Master.[Discount] = tbl_Excel.[Discount]



----
HTH
Dale



Santi said:
I want a master table to have the most up to date information which is based
on an excel file that I get on a monthly basis that has existing items which
has to be updated and, new ones which are not part of the master table but
that I want to include. What structure, queries or tables do I need to make
this happen? Any guidance is greatly appreciated.

*I want to be able to update and include new information if needed on the
following common fields: Item number, Description, Unit price and discount
Click to expand...
Click to expand...
 
Santi,

You will have to replace "tbl_Master" with the name of the table you
descriped in your original post as your master table.

----
HTH
Dale



Santi said:
Dale,

Thank you for the valuable information, but I tried doing the Upsert query
that you suggested using your example and I get the following message: the MS
Access database engine cannot find the input table or query 'tbl_Master'.
Make sure it exists and that its name is spelled correctly.

DO YOU KNOW WHY I GET THAT MESSAGE?

Dale Fye said:
Santi,

Before you try this, backup your database.

When faced with this situation, I generally import the data from the Excel
file into a temporary table.

I then write an update query to update the data in your master table from
the temp table, for those items that are in both, something like:

UPDATE tbl_Master
INNER JOIN tbl_Excel
ON tbl_Master.[Item Number] = tbl_Excel.[Item Number]
SET tbl_Master.[Description] = tbl_Excel.[Description],
tbl_Master.[Unit Price] = tbl_Excel.[Unit Price],
tbl_Master.[Discount] = tbl_Excel.[Discount]

You can then write an append query that will append any new records from
tbl_Excel into tbl_Master.

INSERT INTO tbl_master ([Item Number], [Description], [Unit Price],
[Discount])
SELECT [Item Number], [Description], [Unit Price], [Discount]
FROM tbl_Excel
LEFT JOIN tbl_Master
ON tbl_Excel.[Item Number] = tbl_Master.[Item Number]
WHERE tbl_Master.[Item Number] IS NULL

An alternative method is to create what some people call an UPSERT query,
which is basically an update query which uses a right join rather than an
inner join. Because of the right join the result is that Access will update
those records that already exist, and insert those that don't, all in one
step.

It would look like:

UPDATE tbl_Master
RIGHT JOIN tbl_Excel
ON tbl_Master.[Item Number] = tbl_Excel.[Item Number]
SET tbl_Master.[Description] = tbl_Excel.[Description],
tbl_Master.[Unit Price] = tbl_Excel.[Unit Price],
tbl_Master.[Discount] = tbl_Excel.[Discount]



----
HTH
Dale



Santi said:
I want a master table to have the most up to date information which is based
on an excel file that I get on a monthly basis that has existing items which
has to be updated and, new ones which are not part of the master table but
that I want to include. What structure, queries or tables do I need to make
this happen? Any guidance is greatly appreciated.

*I want to be able to update and include new information if needed on the
following common fields: Item number, Description, Unit price and discount
Click to expand...
Click to expand...
Click to expand...
 
Dale,

Thank you again, but I don’t understand why this is still not working what
am I doing wrong???. Below is the SQL that I’m typing and I get this message
“Type mismatch in expressionâ€


SQL view:

UPDATE Master
RIGHT JOIN NYS
ON Master.[SWS CODE#] =NYS.[SWSCODE]
SET Master.[DESCRIPTION] = NYS.[Description],
Master.[UNIT PRICE] = NYS.[FOB],
Master.[COMMENTS] = NYS.[Disc $]



Dale Fye said:
Santi,

You will have to replace "tbl_Master" with the name of the table you
descriped in your original post as your master table.

----
HTH
Dale



Santi said:
Dale,

Thank you for the valuable information, but I tried doing the Upsert query
that you suggested using your example and I get the following message: the MS
Access database engine cannot find the input table or query 'tbl_Master'.
Make sure it exists and that its name is spelled correctly.

DO YOU KNOW WHY I GET THAT MESSAGE?

Dale Fye said:
Santi,

Before you try this, backup your database.

When faced with this situation, I generally import the data from the Excel
file into a temporary table.

I then write an update query to update the data in your master table from
the temp table, for those items that are in both, something like:

UPDATE tbl_Master
INNER JOIN tbl_Excel
ON tbl_Master.[Item Number] = tbl_Excel.[Item Number]
SET tbl_Master.[Description] = tbl_Excel.[Description],
tbl_Master.[Unit Price] = tbl_Excel.[Unit Price],
tbl_Master.[Discount] = tbl_Excel.[Discount]

You can then write an append query that will append any new records from
tbl_Excel into tbl_Master.

INSERT INTO tbl_master ([Item Number], [Description], [Unit Price],
[Discount])
SELECT [Item Number], [Description], [Unit Price], [Discount]
FROM tbl_Excel
LEFT JOIN tbl_Master
ON tbl_Excel.[Item Number] = tbl_Master.[Item Number]
WHERE tbl_Master.[Item Number] IS NULL

An alternative method is to create what some people call an UPSERT query,
which is basically an update query which uses a right join rather than an
inner join. Because of the right join the result is that Access will update
those records that already exist, and insert those that don't, all in one
step.

It would look like:

UPDATE tbl_Master
RIGHT JOIN tbl_Excel
ON tbl_Master.[Item Number] = tbl_Excel.[Item Number]
SET tbl_Master.[Description] = tbl_Excel.[Description],
tbl_Master.[Unit Price] = tbl_Excel.[Unit Price],
tbl_Master.[Discount] = tbl_Excel.[Discount]



----
HTH
Dale



:

I want a master table to have the most up to date information which is based
on an excel file that I get on a monthly basis that has existing items which
has to be updated and, new ones which are not part of the master table but
that I want to include. What structure, queries or tables do I need to make
this happen? Any guidance is greatly appreciated.

*I want to be able to update and include new information if needed on the
following common fields: Item number, Description, Unit price and discount
Click to expand...
Click to expand...
Click to expand...
 
That error code implies that the data types of the fields are not the same.

Unfortunately, when you import data from Excel, Microsoft has decided that
it should decide for you what the field types are, so I would recommend
importing the Excel data into a table which you have already designed. The
easiest way to do this is to import the Excel worksheet into a new table.
Then open that new table and make sure the field data types are correct, if
not, correct themThen, in the future, import the Excel file into this table

My first step would be to open each of the tables in design view and confirm
that the datatypes of the matching fields are the same. My guess is that you
have a text string in the data coming from EXCEL that you are trying to put
into a numeric field.

I'd start with the [Unit Price] and [FOB] fields. If [FOB] is actually
text, but contains numeric info, then you could change that line like:

Master.[UNIT PRICE] = Val(NYS.[FOB]),

---
HTH
Dale



Santi said:
Dale,

Thank you again, but I don’t understand why this is still not working what
am I doing wrong???. Below is the SQL that I’m typing and I get this message
“Type mismatch in expressionâ€


SQL view:

UPDATE Master
RIGHT JOIN NYS
ON Master.[SWS CODE#] =NYS.[SWSCODE]
SET Master.[DESCRIPTION] = NYS.[Description],
Master.[UNIT PRICE] = NYS.[FOB],
Master.[COMMENTS] = NYS.[Disc $]



Dale Fye said:
Santi,

You will have to replace "tbl_Master" with the name of the table you
descriped in your original post as your master table.

----
HTH
Dale



Santi said:
Dale,

Thank you for the valuable information, but I tried doing the Upsert query
that you suggested using your example and I get the following message: the MS
Access database engine cannot find the input table or query 'tbl_Master'.
Make sure it exists and that its name is spelled correctly.

DO YOU KNOW WHY I GET THAT MESSAGE?

:

Santi,

Before you try this, backup your database.

When faced with this situation, I generally import the data from the Excel
file into a temporary table.

I then write an update query to update the data in your master table from
the temp table, for those items that are in both, something like:

UPDATE tbl_Master
INNER JOIN tbl_Excel
ON tbl_Master.[Item Number] = tbl_Excel.[Item Number]
SET tbl_Master.[Description] = tbl_Excel.[Description],
tbl_Master.[Unit Price] = tbl_Excel.[Unit Price],
tbl_Master.[Discount] = tbl_Excel.[Discount]

You can then write an append query that will append any new records from
tbl_Excel into tbl_Master.

INSERT INTO tbl_master ([Item Number], [Description], [Unit Price],
[Discount])
SELECT [Item Number], [Description], [Unit Price], [Discount]
FROM tbl_Excel
LEFT JOIN tbl_Master
ON tbl_Excel.[Item Number] = tbl_Master.[Item Number]
WHERE tbl_Master.[Item Number] IS NULL

An alternative method is to create what some people call an UPSERT query,
which is basically an update query which uses a right join rather than an
inner join. Because of the right join the result is that Access will update
those records that already exist, and insert those that don't, all in one
step.

It would look like:

UPDATE tbl_Master
RIGHT JOIN tbl_Excel
ON tbl_Master.[Item Number] = tbl_Excel.[Item Number]
SET tbl_Master.[Description] = tbl_Excel.[Description],
tbl_Master.[Unit Price] = tbl_Excel.[Unit Price],
tbl_Master.[Discount] = tbl_Excel.[Discount]



----
HTH
Dale



:

I want a master table to have the most up to date information which is based
on an excel file that I get on a monthly basis that has existing items which
has to be updated and, new ones which are not part of the master table but
that I want to include. What structure, queries or tables do I need to make
this happen? Any guidance is greatly appreciated.

*I want to be able to update and include new information if needed on the
following common fields: Item number, Description, Unit price and discount
Click to expand...
Click to expand...
Click to expand...
 
Santi said:
I want a master table to have the most up to date information which is
based
on an excel file that I get on a monthly basis that has existing items
which
has to be updated and, new ones which are not part of the master table but
that I want to include. What structure, queries or tables do I need to
make
this happen? Any guidance is greatly appreciated.

*I want to be able to update and include new information if needed on the
following common fields: Item number, Description, Unit price and discount
Click to expand...
 
Back
Top