Solved

Access Append New Items Query

Posted on 2016-09-19
7
49 Views
Last Modified: 2016-11-03
I have the attached access database and have 2 tables.

tbl_import -  is all new information directly from companies.
tbl2 - is the table i need to append the import to.

Is there a way to add criteria to my qry_app to make it so it checks for the vendor name and record type not equal to "Direct"? For example, i want all the items in tbl_import to go to table 2 since the record type is "Direct". If it were "AP", company 1 & 5 would not be appended.

I cannot add a column to my import.

Kelly
Database71.accdb
0
Comment
Question by:kwarden13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 41805325
Your example as defined doesn't make any sense.  For starters, your terminology is inconsistent.  A customer is not necessarily a vendor.  The two are not interchangeable and there is no Record type in the test with a value of "direct" so that is also confusing.

1. How do you determine the record type when you are adding a new record to tbl2?  Running the query appends a new row with the record type being empty.
2. Joining two tables on two non-key fields will result in duplicaterows being returned.  The only reason the query is not doing that now is because you are using a left join and checking for a null value.  If you remove that condition in order to satisfy your request, the results will increase exponentially as you run the append query multiple times.
0
 

Author Comment

by:kwarden13
ID: 41805678
I was trying to build a quick example so its not perfect. Anything in the import table assume is "Direct". Essentially I need a query to check if the combination of vendor name and record type is unique. I have a situation where multiple vendors have the same name but different record types.

FYI, do not jump down my throat about vendor id or creating a key. My actual data has one. This is just an example.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41805702
Examples need to reflect real life.  What is the actual primary key of each table since that does make a difference in this example?  If the primary key is an autonumber, then you will need a unique index on the combination of vendor and rate type.

I still don't know what default you want for rate type when you insert a row.  If rate type isn't in the input, how does it get populated?  If the field is part of a compound primary key it cannot be null.  It can be null as part of a unique index but that will cause other problems so make it a required field.
0
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 

Author Comment

by:kwarden13
ID: 41816881
Hi Pat-

I modified my example so it is easier to understand. Also, came up with a solution in the meantime. My goal is to take all the new vendors from the import_spend table and add them to the tbl_vendor.

Initially, I added them to tbl_vendor and then updated the record type and other attributes. However, in doing this, the data was not correct since to update I was using a between formula with a text field. So now in my import, I created a new field for vendor2 (contains a numeric id) and for the record type. I will run the formula prior to appending new records.

So queries are ran in this order:
qry_format_id
qupd_sp12
qupd_123
Database71.accdb
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41816977
1. Column and table names should not contain embedded spaces or special characters.
2. Adding a unique index on VendorCD (I only use the ID suffix when the field is an autonuber) will allow you to simply append records from the Import table to the client table.  Duplicates will simply be rejected.

Your example is still confused but I changed the tables and queries so you can import new clients without duplicating them assuming that the vendorclientnumbers are unique.

I'm not sure why you are trying to format the numbers but I did that also.
Database71Modified.accdb
0
 

Author Comment

by:kwarden13
ID: 41816984
Thanks for looking Pat. I'm going to check it out when I get home. I was formatting the numbers because they are text when they are imported ( other data starts with letters) so I needed to format to number for the between statement to work for record type field.

Kelly
0

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question