Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

Access Append New Items Query

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
kwarden13
Asked:
kwarden13
  • 3
  • 3
1 Solution
 
PatHartmanCommented:
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
 
kwarden13Author Commented:
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
 
PatHartmanCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kwarden13Author Commented:
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
 
PatHartmanCommented:
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
 
kwarden13Author Commented:
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now