Elegant ways to handle multiple queries within a single sub (1 Viewer)

Richard Horne

Member
Local time
Today, 01:32
Joined
Oct 15, 2020
Messages
55
I think what @Pat Hartman and @CJ_London are getting at is that you don't need to loop the lines individually to get your query results.
you can join to your correct data and simply produce a list of items that don't match.

That's a good idea and is much more efficient. I could apply that logic to most of my other checks, too.

I think because I'm just at the stage of trying to actually get everything working, I haven't really considered efficiencies yet.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:32
Joined
Feb 19, 2002
Messages
42,981
I don't need any help building this specific system
We disagree based on what you've told us. You are looking for "elegant" code when what you need is queries that will save you hundreds or perhaps thousands of lines of inelegant code.

When working with Access, your priority is:
1. Action queries
2. Joins rather than domain functions
3. Property settings
4. When all else fails, write code.

Working with third generation systems, I've written my million lines of code and don't need the practice. That's why I love Access.
 

RogerCooper

Registered User.
Local time
Yesterday, 18:32
Joined
Jul 30, 2014
Messages
277
I do this sort of thing frequently. First you need to create some tables which have the correct fields you need to process the orders. Make sure you have a field for recording errors. You should also have a consistent place were you save the spreadsheet and link to it. The steps are then.

1. Empty out your order tables
2. Populate your order tables with append queries using the link to the spreadsheet
3. Run additional update queries to fill in missing data
4. Then run your checking queries as update queries, that append the error name to your error field
5. If the error field is null, you can process the order into your system
6. Have query for the non-null values of the error field which can view/print/e-mail

Note that each query is run against the entire batch of orders, not against each individual order.

I actually use macros because they are easier work with I am just running a series of queries.

This an easier way of handling the matching issues. To make sure that you have a product that matches the NSN you need to do the following.

1. In your order table you need fields for NSN and product
2. You need to have table that cross references NSN's and products with a primary key of NSN
3. After you import the data with the NSN, use an update query to fill in the product from the cross reference
4. You have need a checking update query which adds "Missing Product:" to the error field when the product is null
5. You have a separate select query which pulls up all the "Missing Product" situations so you can get them added to the cross reference

You don't need to be counting NSN's. Furthermore, if an order lacks a product match, you probably want all the missing product matches, not just the first one.
 

Users who are viewing this thread

Top Bottom